Counting specific char's in a string

[MLH]

MyString = "All men are created equal"
Debug.PrintLen( MyString)

Now that's easy. But how about just counting
the letter "e"? Or, if I were curious to know
how many commas were in the string. How
might one do this in a query?

Suppose a table had single string field with
100 records, each containg a sentence
from a textbook. What would the SQL look
like in a query that listed all 100 sentences
and the number of e's appearing in each
sentence?

[Allen Browne]

Replace the character with nothing, and the difference in length is the
count of the character instances.

The query would be something like this:
SELECT Len([MyField]) - Len(Replace([MyField], ",", "")) AS CountOfCommas
FROM Table1;

This should work in Access 2002, 2003, and 2007.
It won't work in A97 or earlier.
In A2000, you may strike this problem:
http://support.microsoft.com/kb/225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthStat e.netwrote in message
news:rq******** *************** *********@4ax.c om...

MyString = "All men are created equal"
Debug.PrintLen( MyString)

Now that's easy. But how about just counting
the letter "e"? Or, if I were curious to know
how many commas were in the string. How
might one do this in a query?

Suppose a table had single string field with
100 records, each containg a sentence
from a textbook. What would the SQL look
like in a query that listed all 100 sentences
and the number of e's appearing in each
sentence?

[Jeff Smith]

Look for InStr in the help file

"MLH" <CR**@NorthStat e.netwrote in message
news:rq******** *************** *********@4ax.c om...

MyString = "All men are created equal"
Debug.PrintLen( MyString)

Now that's easy. But how about just counting
the letter "e"? Or, if I were curious to know
how many commas were in the string. How
might one do this in a query?

Suppose a table had single string field with
100 records, each containg a sentence
from a textbook. What would the SQL look
like in a query that listed all 100 sentences
and the number of e's appearing in each
sentence?

[raskew via AccessMonster.com]

Hi -

This will work in A97.

Function StrCount(ByVal TheStr As String, theItem As Variant) As Integer
'------------------------------------------------------------------
' Purpose: Counts the numbers of times an item occurs
' in a string.
' Coded by: raskew
' Arguments: TheStr: The string to be searched.
' TheItem: The item to search for.
' Returns: The number of occurences as an integer.
'
' Note: To test: Type '? StrCount("The quick brown fox jumped over
' the lazy dog", "the") in the debug window.
' The function returns 2.
'------------------------------------------------------------------
Dim i As Integer
Dim j As Integer
Dim placehold As Integer
Dim strHold As String
Dim itemhold As Variant

strHold = TheStr
itemhold = theItem
j = 0

If InStr(1, strHold, itemhold) 0 Then
While InStr(1, strHold, itemhold) 0
placehold = InStr(1, strHold, itemhold)
j = j + 1
strHold = Mid(strHold, placehold + Len(itemhold))
Wend
End If
StrCount = j
End Function

HTH - Bob

Jeff Smith wrote:

>Look for InStr in the help file

>MyString = "All men are created equal"
Debug.PrintLen (MyString)

[quoted text clipped - 10 lines]

>and the number of e's appearing in each
sentence?

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200703/1

[MLH]

Totally elegant