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

(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



"BillGlad" <BillGlad@discussions.microsoft.com> wrote in message 
news:0EAAE878-CBC4-4251-BDB0-5FD1C4349903@microsoft.com...
> Which function will give me the easiest route to counting a string withing 
> a
> string using MS Access VBA? 

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) 



"BillGlad" wrote:

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