How many time a string appears in a text

[galsaba]

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

[Ken Snell]

Here is a function that you can use:

Public Function CountStringOccu rrence(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

CountStringOccu rrence = 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 CountStringOccu rrence = _
CountStringOccu rrence + 1
Next intC
End Function
--

Ken Snell
<MS ACCESS MVP>

"galsaba" <ga*****@aol.co m> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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

[Terry Kreft]

Function HowMany( _
Optional ByVal String1, _
Optional ByVal String2, _
Optional ByVal Compare As VbCompareMethod = vbBinaryCompare _
) As Integer

Dim intStart As Integer
Dim intCount As Integer
Dim intInstr As Integer
intCount = 0

intStart = 1
intInstr = InStr(intStart, String1, String2, Compare)

Do While intInstr > 0
intCount = intCount + 1
intStart = intInstr + Len(String2)
intInstr = InStr(intStart, String1, String2, Compare)
Loop
HowMany = intCount
End Function
--
Terry Kreft
MVP Microsoft Access
"galsaba" <ga*****@aol.co m> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.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

[Lyle Fairfield]

"galsaba" <ga*****@aol.co m> wrote in news:1115131189 .653532.73100
@z14g2000cwz.go oglegroups.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.

I think I woud use a recursive sub here. Air code ...

'Option Compare Database
'Option Compare Binary
Option Compare Text
Option Explicit

Sub temp()
Dim Occurs As Long
CountOccurrence s "My name is John Mmm Smith, " _
& "and my wife is Katy Smith-Smithm", _
"m", Occurs
MsgBox Occurs '10
End Sub

Sub CountOccurrence s( _
ByVal vInWhat As String, _
ByVal vCountWhat As String, _
ByRef rOccurs As Long, _
Optional ByVal vStart As Long)

vStart = InStr(vStart + 1, vInWhat, vCountWhat)
If (vStart = 0) Then
Exit Sub
Else
rOccurs = rOccurs + 1
CountOccurrence s vInWhat, vCountWhat, rOccurs, vStart
End If
End Sub

I think I might be in the minority here, though.

--
Lyle

"The aim of those who try to control thought is always the same. They find
one single explanation of the world, one system of thought and action that
will (they believe) cover everything; and then they try to impose that on
all thinking people."
- Gilbert Highet

[jimfortune]

The most elegant solution, but not the fastest or most efficient:

http://groups-beta.google.com/group/...8bcc0ce3?hl=en

I haven't found the best way to do this.

James A. Fortune

I'm also looking for a "Blue Ocean" strategy for Access.

[Terry Kreft]

Yes very nice, but the declaration should be (for most purposes)
Public Function CountSubstrings (ByVal strIn As String, strFind As String) As
Integer
--
Terry Kreft
MVP Microsoft Access

<ji********@com pumarc.com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

The most elegant solution, but not the fastest or most efficient:

http://groups-beta.google.com/group/...8bcc0ce3?hl=en
I haven't found the best way to do this.

James A. Fortune

I'm also looking for a "Blue Ocean" strategy for Access.