Count a string withing a string - VBA

[BillGlad]

Which function will give me the easiest route to counting a string withing a
string using MS Access VBA?

 

[vanderghast]

(len(originalString) - len( replace( originalString, substringYouCount,
vbNullString)) ) / len(subStringYouCount)


Replacing (temporary) each substring occurence, in the original string, by a
zero length string reduce the original string by a total of Number of time
the substring is found * length of the substring. That is what the
formulation here up does, in one line of code.

if you use it in a query, change the predefined constant vbNullString by
"" , two double quotes side by side.


Vanderghast, Access MVP

 

[Jack Leach]

Public Function GetStrOccurrences(strSearchFor As String _
, strSearchString As String _
, Optional intCompare = vbTextCompare) As Integer
'Source: datAdrenaline
'http://www.utteraccess.com/forums/showflat.php? _
' Cat=&Number=1833584&page=&view=&sb=&o=&fpart=&vc=

'Searchs through the strSearchString for strSearchFor and returns the number
'of times that strSearchFor occurs

If Len(strSearchFor) > 0 Then _
GetStrOccurrences = UBound(Split(strSearchString, _
strSearchFor, , _
intCompare))

End Function




I came across this a few weeks ago. Might come in handy. Incidentally, the
link has a few other methods, as well as examples of how each one works.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)