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

How many time a string appears in a text

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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


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

Nov 13 '05 #2

P: n/a

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.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

Nov 13 '05 #3

P: n/a
"galsaba" <ga*****@aol.com> wrote in news:1115131189.653532.73100
@z14g2000cwz.googlegroups.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
CountOccurrences "My name is John Mmm Smith, " _
& "and my wife is Katy Smith-Smithm", _
"m", Occurs
MsgBox Occurs '10
End Sub

Sub CountOccurrences( _
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
CountOccurrences 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
Nov 13 '05 #4

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

Nov 13 '05 #5

P: n/a
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********@compumarc.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.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.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.