By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 988 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Count only certain word in memo

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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.
Nov 13 '05 #3

P: n/a
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
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.