Counting Characters (1 Viewer)

[striker]

Is there any way that I can count the number of times a particular text character appears in a text field and also the positions of that character(s) within its field and then use that info in a query.

 

[Howlsta]

You can use the InStr function to search for a particular character.

So say if you were searching for the letter a, you would do as follows

InStr(0,YourString,a)

0 represents the start (if not specified it will automatically start searching at the first character. Look up InStr in help.

But as you want to find all occurances of the character, you will have to do a loop. A For...Next loop may do the trick. So, you'll have to store the position of the first occurance in a variable and increment it by one so the start position changes after each repetition of the character is found.

Let me know how you get on.

HTH

Rich

 

[Howlsta]

The code would probably look like this. I suggest you use it in a form rather than a query. You might be able to make it work for the query, do any of the 'experts' know how this is done???

Private Sub yourCommandButton_Click() '

Dim MyString As String
Dim MyLen As Integer
Dim MyPos As Integer
Dim i As Integer
Dim Count As Integer
Dim SearchChar As String

i = 1
SearchChar = "a" ' change, so that it takes the string from a text box

MyString = Me.txtCheck.Text
MyLen = Len(MyString)

For i = 1 To MyLen
MyPos = InStr(i, MyString, SearchChar)
Count = Count + 1
i = MyPos
Next

MsgBox "There are " & Count & " " & SearchChar & " in the text"
End Sub

 

[striker]

One hit wonder

The reason I want to do this is to create a keyword lookup combo box from an existing datatable. The table is made up of two fields of which on is a id number field and the other is a memo field which looks as though it has had word documents pasted into the field.

What i am trying to do is seperate the spaces out and the create a list of keywords for future reference. So this will only have to be run once to sort out existing data and then future data input will have the keywords typed in when the record is saved. This is all a bit strange the way they want this done.

Its a shame I cant do this in a query because i could then have made a keyword table in one foul swoop.

Cheers for the help and the pointers I've not had that much to do with strings so didn't have a clue on how to approach the problem.

Thanks again

Steve.