Here is a function that you can use:
Public Function CountStringOccurrence(ByVal sStr As String, ByVal sChar As
String, _
Optional yCase As Boolean = True) As Long
' Ken Snell 3 May 2005
'**THIS FUNCTION COUNTS THE NUMBER OF TIMES A TEXT SUBSTRING IS FOUND IN A
TEXT STRING. THE
'**SEARCH CAN BE CASE-SENSITIVE (yCase is True) OR CASE-INSENSITIVE (yCase
is False).
'**THE FUNCTION'S VALUE IS THE NUMBER OF TIMES THE TEXT SUBSTRING IS FOUND.
' intC is used as a loop counter
' sChar is the text substring for which the search is to be done
' sStr is the text string that is to be searched
' yCase is the "case sensitivity" flag (True for case sensitivity; False for
case
' insensitivity)
Dim intC As Long, intLen As Long
CountStringOccurrence = 0
intLen = Len(sChar)
If yCase = False Then
'user desires case insensitivity, so make everything Upper Case
sStr = UCase(sStr)
sChar = UCase(sChar)
End If
For intC = 1 To Len(sStr)
'if character is found, increment the counter
If Mid(sStr, intC, intLen) = sChar Then CountStringOccurrence = _
CountStringOccurrence + 1
Next intC
End Function
--
Ken Snell
<MS ACCESS MVP>
"galsaba" <ga*****@aol.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
How can I find how many time a string appears in a text.
Is there a function in access that does it?
For example,
text: "My name is John Smith, and my wife is Katy Smith"
HowMany(text, "Smith")
this will give me 2.
Thanks,
galsaba
