Question Re: InStr or other function

[Joe Blow]

Hello all:

I posed this question about a year ago, and now my news server has dropped
the old responses I received.
Unfortunately, my PC crashed and I cannot find the query that I used, so I
apologetically am going to ask the same question again.

I have a text field and wish to count the # of times a substring shows up.

For example:

I want to count the # of times the word "BUS" shows up in the following
phrase:
THE BUS IS A VERY BUSY BUSINESS. The query would return back a "3".

I need to write this as an expression in a query because I need multiple
columns counting multiple substrings within the same text field.


Can someone help?


Thanks,

-Joe

 

[Jason Lepack]

' Put this function in a module so that you can access it
Public Function countWords(strToLookIn As String, strToFind As String)
As Integer
Dim i As Integer
i = 0
countWords = -1
Do
i = i + 1
i = InStr(i, strToLookIn, strToFind)
countWords = countWords + 1
Loop Until i = 0
End Function

Cheers,
Jason Lepack

 

[Guest]

I don't know what you found before, but this will do it:

Public Function HowMany(strString, strFind) As Integer
Dim intPos As Integer
Dim intLen As Integer
Dim intFindCount As Integer
Dim blnDone As Boolean

intLen = Len(strFind)
intPos = 1

Do Until blnDone
intPos = InStr(intPos, strString, strFind)
If intPos = 0 Then
Exit Do
Else
intFindCount = intFindCount + 1
End If
intPos = intPos + intLen
Loop
HowMany = intFindCount

End Function

You would call it like this:

= HowMany("THE BUS IS A VERY BUSY BUSINESS.", "BUS")

 

[fredg]

Hello all:

I posed this question about a year ago, and now my news server has dropped
the old responses I received.
Unfortunately, my PC crashed and I cannot find the query that I used, so I
apologetically am going to ask the same question again.

I have a text field and wish to count the # of times a substring shows up.

For example:

I want to count the # of times the word "BUS" shows up in the following
phrase:
THE BUS IS A VERY BUSY BUSINESS. The query would return back a "3".

I need to write this as an expression in a query because I need multiple
columns counting multiple substrings within the same text field.

Can someone help?

Thanks,

-Joe

And I remember replying.
Here is part of my reply:

CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3

The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.

 

[Joe Blow]

Oddly, last time this question was answered, it was the exact same way.
With a module.

Then someone wrote a simple one-liner that I can put right in the query as
an expression which gave me the numbers I was looking for. And everyone
went "WOW!". I agreed and thanked the Access MVP for his help.

I don't mind using a module/code, but was hoping to find the "easy" way out.
I tried using InStr, but that does not seem to be working the way that I
need it to.

It looked something like: BUS_COUNT:COUNT(InStr(TextField), "BUS")

Any ideas?

Thanks again,

-Joe

 

[Jason Lepack]

Try this in your Field:
UBound(Split(strToLookIn, strToFind))

Cheers,
Jason Lepack

 

[Joe Blow]

You are a genius!

Thank you
Thank you
Thank you


-Joe

And I remember replying.
Here is part of my reply:

CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3

The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.

 

[Jason Lepack]

I'm curious. If you have a lot of data in that table of yours, could
you make two different queries and in one, use the function taht I
gave you first, and then one using that little one-liner and compare
the performance.

Cheers,
Jason Lepack

You are a genius!

Thank you
Thank you
Thank you

-Joe

And I remember replying.
Here is part of my reply:

CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3

The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.

 

[Jason Lepack]

NM, UBound doesn't work in Query Designer... duh...

I'm curious. If you have a lot of data in that table of yours, could
you make two different queries and in one, use the function taht I
gave you first, and then one using that little one-liner and compare
the performance.

Cheers,
Jason Lepack

You are a genius!

Thank you
Thank you
Thank you

-Joe

And I remember replying.
Here is part of my reply:
CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3
The /3 part is whatever the number of characters in the search string
(i.e. "bus") is.

- Show quoted text -

 

[John W. Vinson]

Try this in your Field:
UBound(Split(strToLookIn, strToFind))

whooo....

CUTE, Jason!

John W. Vinson [MVP]

 

[John W. Vinson]

NM, UBound doesn't work in Query Designer... duh...

oh rats... I was afraid it was too cute...
<g>

John W. Vinson [MVP]

 

[Jason Lepack]

I know! That's what you get for posting untested code though...