Count Specific Characters

  • Thread starter Thread starter James Buck
  • Start date Start date
J

James Buck

In a query I need to count the number of times a specific character shows up
for each record.
For example I need to know how many times "x" shows up in "xtr hxtgxm". In
this example I would want it to return 3.

Thanks for your help.
 
James said:
In a query I need to count the number of times a specific character shows up
for each record.
For example I need to know how many times "x" shows up in "xtr hxtgxm". In
this example I would want it to return 3.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to write a function to count the characters. Call that
function from the query. E.g.:

SELECT RecID, CountChars(ColumnName) As CharCount
FROM TableName
.... etc. ...

CountChars() is the function you need to write.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIQyRYechKqOuFEgEQKilACgyd2yJppJj4QmRdm8WLr9SnfL/ywAoOk6
/yqFaU0WJDwBeerakFlm/tL3
=hIU8
-----END PGP SIGNATURE-----
 
In a query I need to count the number of times a specific character shows up
for each record.
For example I need to know how many times "x" shows up in "xtr hxtgxm". In
this example I would want it to return 3.

Thanks for your help.
Click to expand...

Paste the following function into a Module.

Public Function CountOccurrences(strFull As String, strSearch As
String) As Integer

' This function will find how many occurances there are
' of a string within a string.

Dim intX As Integer
Dim intY As Integer
intX = InStr(1, strFull, strSearch)
Do While intX <> 0
intY = intY + 1
intX = InStr(intX + 1, strFull, strSearch)
Loop
CountOccurrences = intY

End Function
==========

You can call it from a query:
CountChars:CountOccurrences([FieldName],"x")

Add criteria to the query to restrict Null fields, or add error
handling to the function.
 
Back
Top