Count instances of a character in a string

[mwheads]

Hi all

I would like to know how to count instances of a character within a string. The character is always ">"

The way i did it in excel that worked for me was:
=LEN(A1)-LEN(SUBSTITUTE(A1,">",""))

I need something similar (or better) for Access.

thanks

 

[RoyVidar]

I'd try the Replace function;-)

[tt]public function charcount(varString as variant, _
optional strReplace as string = "<") as long
if len(strString)>0 then
charcount=len(strString)-len(replace(strString, strReplace,""))
else
charcount=0
end if
end function[/tt]

If strString is "This < is < a < test" and strReplace is "<", a form control should show 3 with the following controlsource

=charcount(txtText)

If you need to count other characters, just alter the parameter to for instance

=charcount(txtText,"|")

Roy-Vidar

 

[mwheads]

Hi Roy-Vidar

I am not sure what strString is referring to, do i need to Dim strstring?

I am not doing something right as my result is always a #Name?

on my controlsource on my form in [Text103], I have got =charcount([Text101])

 

[RoyVidar]

Gee - wrong declaration of the passed parameter - it's supposed to be either varString or strString all places. The code should work as a function within a form module, but perhaps put it in a standard module, so you can use it elsewhere (in VBE - insert | module)

Roy-Vidar

 

[mwheads]

Thanks, that works great.