Count Specific Characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in a table called "textline1" that contains text that contains "$". I want to know how many times the "$" show up in each field, because I need this to use as a criteria for manipulation. For instance record 1 contains "Project ($1,000), Project 2 ($2,000)". I need this to come back with 2 because it contains 2 "$".
Thanks.
 
I have a field in a table called "textline1" that contains text that contains "$". I want to know how many times the "$" show up in each field, because I need this to use as a criteria for manipulation. For instance record 1 contains "Project ($1,000), Project 2 ($2,000)". I need this to come back with 2 because it contains 2 "$".
Thanks.
Click to expand...

You'll need to write your own User Defined function.
Place the following in a Module:

Public Function CountChrs(strIn As String) As Integer
Dim intX As Integer
Dim intY As Integer
intX = InStr(strIn, "$")
Do While intX <> 0
intY = intY + 1
intX = InStr(intX + 1, strIn, "$")
Loop

CountChrs = intY
End Function
=====

You can then call it from a query
Exp:CountChrs([MemoField])
Set the query criteria to
Where [MemoField] Is Not Null

or from an unbound control on a report or form:
=IIf(Not IsNull([MemoField]),CountChrs([MemoField]),"")
 
Back
Top