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

Counting specific char's in a string

P: n/a
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?
Mar 4 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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**@NorthState.netwrote in message
news:rq********************************@4ax.com...
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?
Mar 4 '07 #2

P: n/a
Look for InStr in the help file

"MLH" <CR**@NorthState.netwrote in message
news:rq********************************@4ax.com...
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?

Mar 4 '07 #3

P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200703/1

Mar 5 '07 #4

P: n/a
MLH
Totally elegant
Mar 6 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.