Calculate # of occurrences of string in a field

[martlaco]

Thought I'd be able to find out easily, but.. here I am...

How can I query the number of occurrences of a string in a field, e.g.:

if a name field has the text: Martin Lacoste

how can I get it to tell me there are two "a"s in the field?

I can use InStr to find one, and make a few more queries to find a few subsequent, but the data I need to search could have 30-40 occurrences of the desired text in a field.

Ideas?
Thanks!
Martin Lacoste

 

[boblarson]

This should do it for you. You would feed this function the string you want to check and the letter you want to check for and it will return the count (I think there's a way to make it even more efficient but I can't remember how).

Public Function GetLetterCount(strInput As String, strLetter As String) As Integer
Dim intCount As Integer
Dim intLetterCount As Integer
intCount = 1
Do Until intCount = Len(strInput) + 1
    If Mid(strInput, intCount, 1) = strLetter Then
        intLetterCount = intLetterCount + 1
    End If
    intCount = intCount + 1
    GetLetterCount = intLetterCount
Loop
End Function

 

[martlaco]

re: Calculate # of occurrences...

Sorry for not getting back to you sooner, Bob - thanks very much for your reply.

If you'd be able to, would you be able to walk me through how to use this (or is there some help file online that might guide me there)? I'm assuming this goes into a module, yes? Have used modules a bit in the past, but it's been a while.
And then, how do I feed the string into the function?

Forgive my 'newbieness' - and certainly don't sweat it if you don't have the time to address this as a result - it could be that the learning curve is just too steep at the moment.

Thanks again for your help!
Martin

 

[boblarson]

Yes, put it into a Standard Module (not a form module) and then from wherever you need it call it like:

In a Query:

MyStringCount:GetLetterCount([YourfieldNameHere],"TheLetterYouWantCountedHere")

Or if filling in a text box - in the after update event of some other text box that has the data entered:

Me.YourTextBoxForCountNameHere = GetLetterCount(Me.YourTextBoxNameWithTheStringHere, Me.YourTextBoxWithTheLetterToLookForHere)

 

[martlaco]

re: Calculate # of occurrences...

Thanks again for your (very quick!!) reply! I think I'm getting closer...
Here's where I'm at... (btw, should have indicated I'm using Access 2000 - I'm assuming that does not affect things, but, just in case...)

- pasted the code into a module (below the declaration), and named the module "GetLetterCount"

- set up a query called "StringCount", in which I pasted the expression you posted, which I hoped would call the module, but...

The query does not seem to call the module, thus giving me this message:
"Undefined function 'GetLetterCount' in expression'

I'm sure I'm missing something basic to seal the deal... - how do I get the expression in the query to call the module?

Thanks again, Bob
Martin

 

[boblarson]

You do NOT want to name the module the same name as the function. The function name is GetLetterCount so the module itself has to be named something else.

 

[martlaco]

re: Calculate # of occurrences...

Oddly enough, I had wondered about that....

It's running fine!

Thanks so much for your help and patience in walking me through this. Much appreciated!!!

Martin