Hello All:

First off, thank you so much for your help. It is truly appreciated.

I am trying to write an expression in a query that counts how many times a
particular string of data repeats in a text field.

ie: THE BUS IS A VERY BUSY BUSINESS.

I ask, how many times does the exact string "BUS" show up, and the result
should be 3. One for BUS, another for BUSY and another for BUSINESS. Can't
use InStr because that only returns the first position...

Any ideas how to write this column in an Access 2003 query?

Thanks,

Joe



--
Posted via a free Usenet account from http://www.teranews.com

On Mon, 4 Dec 2006 14:53:52 -0800, Joe Blow wrote:

> Hello All:
>
> First off, thank you so much for your help. It is truly appreciated.
>
> I am trying to write an expression in a query that counts how many times a
> particular string of data repeats in a text field.
>
> ie: THE BUS IS A VERY BUSY BUSINESS.
>
> I ask, how many times does the exact string "BUS" show up, and the result
> should be 3. One for BUS, another for BUSY and another for BUSINESS. Can't
> use InStr because that only returns the first position...
>
> Any ideas how to write this column in an Access 2003 query?
>
> Thanks,
>
> Joe
InStr can return more than just the first instance.
it depends upon from where in the string you start looking.
See the lines marked '* below.
In a module:
Function CountString(StringIn as String, LookFor as String) as String
Dim intX as Integer
Dim intY as Integer
intX = InStr(StringIn,LookFor) '* start from the 1st position
Do while intX <> 0
intY = intY + 1

'*start at the intX + 1 position
intX = InStr(intX + 1,StringIn,LookFor)
Loop
CountString = intY
End Function

Call it from a query.

CountInString:CountString([FieldName],"bus")

This also works (without using a mocule).

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.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

"Joe Blow" <(E-Mail Removed)> wrote in message
news:45749a39$0$20605$(E-Mail Removed)
> Hello All:
>
> First off, thank you so much for your help. It is truly appreciated.
>
> I am trying to write an expression in a query that counts how many
> times a particular string of data repeats in a text field.
>
> ie: THE BUS IS A VERY BUSY BUSINESS.
>
> I ask, how many times does the exact string "BUS" show up, and the
> result should be 3. One for BUS, another for BUSY and another for
> BUSINESS. Can't use InStr because that only returns the first
> position...
>
> Any ideas how to write this column in an Access 2003 query?

I think you'll have to call a user-defined function; like this one,
perhaps:

'----- start of code -----
Function fncCountString(LookIn As Variant, LookFor As Variant) As Long

' Returns the number of times the second (string) argument
' is found in the first (string) argument.
'
' Written by: Dirk Goldgar, DataGnostics
' Published for public use -- please retain attribution.

Dim lCount As Long
Dim sLookIn As String
Dim sLookFor As String

If IsNull(LookIn) Or IsNull(LookFor) Then
fncCountString = 0
Else
lCount = UBound(Split(LookIn, LookFor))
If lCount < 0 Then
fncCountString = 0
Else
fncCountString = lCount
End If

End If

End Function
'----- end of code -----

With the above function stored in a standard module, you can call it
from a query if you like.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Fred, CountInString: (Len([FieldName])-Len(Replace([FieldName],"bus","")))/3
worked like a charm.

Smiling happy camper here.


THANKS!!!


Joe


"fredg" <(E-Mail Removed)> wrote in message
news:1ntwqjamvtc5g.10cjty5egtejo$.(E-Mail Removed)...
> On Mon, 4 Dec 2006 14:53:52 -0800, Joe Blow wrote:
>
>> Hello All:
>>
>> First off, thank you so much for your help. It is truly appreciated.
>>
>> I am trying to write an expression in a query that counts how many times
>> a
>> particular string of data repeats in a text field.
>>
>> ie: THE BUS IS A VERY BUSY BUSINESS.
>>
>> I ask, how many times does the exact string "BUS" show up, and the result
>> should be 3. One for BUS, another for BUSY and another for BUSINESS.
>> Can't
>> use InStr because that only returns the first position...
>>
>> Any ideas how to write this column in an Access 2003 query?
>>
>> Thanks,
>>
>> Joe
> InStr can return more than just the first instance.
> it depends upon from where in the string you start looking.
> See the lines marked '* below.
> In a module:
> Function CountString(StringIn as String, LookFor as String) as String
> Dim intX as Integer
> Dim intY as Integer
> intX = InStr(StringIn,LookFor) '* start from the 1st position
> Do while intX <> 0
> intY = intY + 1
>
> '*start at the intX + 1 position
> intX = InStr(intX + 1,StringIn,LookFor)
> Loop
> CountString = intY
> End Function
>
> Call it from a query.
>
> CountInString:CountString([FieldName],"bus")
>
> This also works (without using a mocule).
>
> 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.
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail



--
Posted via a free Usenet account from http://www.teranews.com

"fredg" <(E-Mail Removed)> wrote in message
news:1ntwqjamvtc5g.10cjty5egtejo$.(E-Mail Removed)
> InStr can return more than just the first instance.
> it depends upon from where in the string you start looking.
> See the lines marked '* below.
> In a module:
> Function CountString(StringIn as String, LookFor as String) as String
> Dim intX as Integer
> Dim intY as Integer
> intX = InStr(StringIn,LookFor) '* start from the 1st position
> Do while intX <> 0
> intY = intY + 1
>
> '*start at the intX + 1 position
> intX = InStr(intX + 1,StringIn,LookFor)
> Loop
> CountString = intY
> End Function
>
> Call it from a query.

FWIW, I tried this approach as an alternative to the Split-based version
I posted. I benchmarked them both, and was surprised to find that the
version using Split() was much faster than the version using InStr().

> This also works (without using a mocule).
>
> 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.

Cool! I never thought of this one! Brilliant!

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)