Count only certain word in memo

[Sashafay]

Hi,

How I can count certain word in memo field?

For example I have 15 words in one field and "PLUMING" word repeats in
it 3 times. So I would like my code give me number three as the
answer.

Any help with it will be kindly,
Alex

[Douglas J. Steele]

Assuming you're using Access 2000 or newer, here's a nifty approach:

Private Function CountInstances( _
ByVal ToSearch As String, _
ByVal ToFind As String) As Long

CountInstances = (Len(ToSearch) - _
Len(Replace$(ToSearch, ToFind, vbNullString))) _
\ Len(ToFind)

End Function

What this does is replace each occurrence of the character (or word or
expression) in the string with a null string (""). Assuming that the
character exists in the string, the length of the string will be reduced by
that operation. Knowing how much the length of the string decreased, we can
determine how many times the character occurred in the string.

If you're using Access 97 or earlier, you'll have to write your own
equivalent function to the Replace function. The downloadable database has
one such function in it.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Sashafay" <af******@mwh.com> wrote in message
news:a1**************************@posting.google.c om...

Hi,

How I can count certain word in memo field?

For example I have 15 words in one field and "PLUMING" word repeats in
it 3 times. So I would like my code give me number three as the
answer.

Any help with it will be kindly,
Alex

[Salad]

Douglas J. Steele wrote:

Assuming you're using Access 2000 or newer, here's a nifty approach:

Private Function CountInstances( _
ByVal ToSearch As String, _
ByVal ToFind As String) As Long

CountInstances = (Len(ToSearch) - _
Len(Replace$(ToSearch, ToFind, vbNullString))) _
\ Len(ToFind)

End Function

What this does is replace each occurrence of the character (or word or
expression) in the string with a null string (""). Assuming that the
character exists in the string, the length of the string will be reduced by
that operation. Knowing how much the length of the string decreased, we can
determine how many times the character occurred in the string.
Very, very cool, Douglas.

If you're using Access 97 or earlier, you'll have to write your own
equivalent function to the Replace function. The downloadable database has
one such function in it.

Here's an A97 function.
Public Function CountInstances(ByVal ToSearch As String, _
ByVal ToFind As String) As Long

Dim intStart As Integer
Dim lngPos As Long

intStart = 1

Do While True

lngPos = InStr(Mid(ToSearch, intStart), ToFind)

If lngPos > 0 Then
CountInstances = CountInstances + 1
intStart = intStart + (lngPos - 1) + Len(ToFind)
Else
Exit Do
End If
Loop
End Function

ToSearch can be changed to a variant and then check if ToSearch is null
in case one is not sure each pass will contain a string.

[Sashafay]

Salad <oi*@vinegar.com> wrote in message news:<cQ*****************@newsread3.news.pas.earth link.net>...

Douglas J. Steele wrote:

Assuming you're using Access 2000 or newer, here's a nifty approach:

Private Function CountInstances( _
ByVal ToSearch As String, _
ByVal ToFind As String) As Long

CountInstances = (Len(ToSearch) - _
Len(Replace$(ToSearch, ToFind, vbNullString))) _
\ Len(ToFind)

End Function

What this does is replace each occurrence of the character (or word or
expression) in the string with a null string (""). Assuming that the
character exists in the string, the length of the string will be reduced by
that operation. Knowing how much the length of the string decreased, we can
determine how many times the character occurred in the string.

Very, very cool, Douglas.

If you're using Access 97 or earlier, you'll have to write your own
equivalent function to the Replace function. The downloadable database has
one such function in it.

Here's an A97 function.
Public Function CountInstances(ByVal ToSearch As String, _
ByVal ToFind As String) As Long

Dim intStart As Integer
Dim lngPos As Long

intStart = 1

Do While True

lngPos = InStr(Mid(ToSearch, intStart), ToFind)

If lngPos > 0 Then
CountInstances = CountInstances + 1
intStart = intStart + (lngPos - 1) + Len(ToFind)
Else
Exit Do
End If
Loop
End Function

ToSearch can be changed to a variant and then check if ToSearch is null
in case one is not sure each pass will contain a string.

Thanks guys!!!

Works just great and the way I want it to.

Alex