The Split function suggested by Chuck would seem to be more appropriate.
And if all you want is a function to tell you how many occurrencs of x occur
in y, the following does it:
Public Function CountSubstrings(strIn As String, strFind As String) As
Integer
CountSubstrings = (Len(strIn) -
Len(Replace(strIn, strFind, ""))) \ Len(strFind)
End Function
What this does is make a new version of strIn, replacing every occurrence of
strFind with a zero-length string (""). It then calculates the difference in
length between the length of the original string and the length of the
modified string. Finally, it divides that difference by the length of
strFind.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)
"James Fortune" <jafortun@oakland.edu> wrote in message
news:a6ed3ce7.0405011945.518a6f7d@posting.google.c om...[color=blue]
> "Danny" <dannywork5@hotmail.com> wrote in message[/color]
news:<NGvkc.58860$Gd3.14093644@news4.srv.hcvlny.cv .net>...[color=blue][color=green]
> > HI again
> >
> > Is there a nifty function in access that will:
> >
> > 1. return the amount of occurances of a small string within a larger
> > string?
> >
> > this<br>is<br>a<br>test
> > would return 3 for <br>
> >
> > 2. Parse a string into an array given a separation string like
> >
> > this<br>is<br>a<br>test
> > so for <br> as delimiter
> > array(0) = this
> > array(1) = is
> > array(2) = a
> > array(3) = test
> >
> > Thanks
> > Danny[/color]
>
> I haven't tested this much; it's not very elegant.
>
> Public Function CountSubstrings(strIn As String, strFind As String) As[/color]
Integer[color=blue]
> Dim intCount As Integer
> Dim strTemp As String
> Dim intFound As Integer
>
> 'Count the number of strFind strings within strIn.
> CountSubstrings = 0
> If Len(strIn) = 0 Then Exit Function
> If Len(strFind) > Len(strIn) Then Exit Function
> intFound = InStr(1, strIn, strFind, vbTextCompare)
> If intFound = 0 Then Exit Function
> intCount = 1
> CountSubstrings = 1
> 'See if the string was found at the end of strIn
> If Len(strIn) = intFound - 1 + Len(strFind) Then Exit Function
> 'Chop off up to and including the found string
> 'Note: Add code here to catch contents for an array if desired
> strTemp = Right(strIn, Len(strIn) - intFound - Len(strFind) + 1)
> intFound = InStr(1, strTemp, strFind, vbTextCompare)
> Do While intFound > 0
> intCount = intCount + 1
> If Len(strTemp) = intFound - 1 + Len(strFind) Then
> CountSubstrings = intCount
> Exit Function
> End If
> 'Note: And code here
> strTemp = Right(strTemp, Len(strTemp) - intFound - Len(strFind) + 1)
> intFound = InStr(1, strTemp, strFind, vbTextCompare)
> Loop
> CountSubstrings = intCount
> End Function
>
> You'll want to index on intCount - 1 for a zero based array and I'd use a
> Sub instead of a Function to return the substring count and the array.
>
> James A. Fortune[/color]
