Counting specific characters within a field

[Mike Faulkner]

OS: Windows XP
App: Access XP

I have a field with over 100 characters of data. I want to count the number
of Tildes (~) found within the field and their position.

I want to extract text strings positioned between each occurrence of each
Tilde.

Any help would be appreciated.

Kind Regards
Mike

 

[Arvin Meyer [MVP]]

Try this:

'********** Code Start **********
Function CountCharInString(strIn As String, strChar As String) As Integer
' Counts occurrences of a character in a string
' Arvin Meyer - 12/14/1999

Dim intInstr As Integer
Dim intRet As Integer
intInstr = 0
intRet = 0
Do
intInstr = InStr(intInstr + 1, strIn, strChar)
If intInstr > 0 Then intRet = intRet + 1
Loop While intInstr > 0

CountCharInString = intRet

End Function
'********** Code End **********

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Disclaimer: Any code or opinions are offered here as is. Some of that
code has been well tested for number of years. Some of it is untested
"aircode" typed directly into the post. Some may be code from other
authors. Some of the products recommended have been purchased and
used by the author. Others have been furnished by their manufacturers.
Still others have not been personally tested, but have been
recommended by others whom this author respects.

You can thank the FTC of the USA for making this disclaimer necessary.

 

[Daniel Pineault]

Different way you could approach this, but probably the easiest would be to
use the Split function. Something like (modify as required to suit your
exact needs):

iCounter = 0
sBulkText = Me.YourFormControlName

aBulkText = Split(sBulkText,"~")
For i = lbound(aBulkText ) to ubound(aBulkText )
Debug.Print "Entry " & i & ": " & aBulkText(i)
iCounter = iCounter + 1
Next i

Debug.Print "Total number of entries: " & iCounter
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[John W. Vinson]

OS: Windows XP
App: Access XP

I have a field with over 100 characters of data. I want to count the number
of Tildes (~) found within the field and their position.

I want to extract text strings positioned between each occurrence of each
Tilde.

Any help would be appreciated.

Kind Regards
Mike

In the vein of "there's always another way":

Len([fieldname]) - Len(Replace([fieldname], "~", "")

 

[Mike Faulkner]

Daniel

Excellent many thanks.

Your code updates an individual record displayed in a Form. How would I
update the entire table?

Kind Regards
Mike

 

[kc-mass]

Hi Mike,

I not that you want to extract the data "between Tildes". I assume the
tildes are more delimiters than bracketts. That is there is probably no
tilde in the first or last position of the text.

If that's the case then create a new table (I called mine tblDataPieces) to
hold the pieces of data extracted from the original text. It has two
fields: DataPiece and FK. The DataPiece will hold what you extract and the
FK will hold the primary key of the table you are extracking from.

This little bit of code will extract the data and store it in the new
table. Just paste it into a new module.

Sub PickoutPieces()
Dim Db As DAO.Database
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim strPiece As String
Dim strSource As String
Set Db = CurrentDb
Set rsOld = Db.OpenRecordset("tblDelimData")
Set rsNew = Db.OpenRecordset("tblDataPiece")
rsOld.MoveFirst
Do While Not rsOld.EOF
strSource = rsOld!DelimData
Do While Len(strSource) <> 0
If InStr(strSource, "~") <> 0 Then
strPiece = Left(strSource, InStr(strSource, "~") - 1)
strSource = Mid(strSource, InStr(strSource, "~") + 1)
Else
strPiece = strSource
End If
rsNew.AddNew
rsNew!FK = rsOld!PK
rsNew!DataPiece = strPiece
rsNew.Update
Loop
rsOld.MoveNext
Loop
Set Db = Nothing
Set rsOld = Nothing
Set rsNew = Nothing
End Sub

If you then want to know how many tildes are in each of the original table's
records, use a query like this:

SELECT tblDataPiece.FK, Count([DataPiece])-1 AS Tildes
FROM tblDataPiece
GROUP BY tblDataPiece.FK;

Regards

Kevin