Douglas J. Steele's CountInstances() function...

[MLH]

Access 97 doesn't have Replace$ function -
at least I couldn't find it. Can someone tell me
what the command does so I can adapt Doug's
code for my own use in A97?

Private Function CountInstances( _
ByVal ToSearch As String, _
ByVal ToFind As String) As Long

CountInstances = (Len(ToSearch) - _
Len(Replace$(ToSearch, ToFind, vbNullString))) _
\ Len(ToFind)

End Function

[CDMAPoster]

On Aug 9, 1:38 pm, MLH <[e-mail]:

Access 97 doesn't have Replace$ function -
at least I couldn't find it. Can someone tell me
what the command does so I can adapt Doug's
code for my own use in A97?

Private Function CountInstances( _
ByVal ToSearch As String, _
ByVal ToFind As String) As Long

CountInstances = (Len(ToSearch) - _
Len(Replace$(ToSearch, ToFind, vbNullString))) _
\ Len(ToFind)

End Function

The Replace function tries to replace inside a string the occurrences
of one substring with another. I think its speed can be increased
easily by utilizing the 'start' argument of the InStr function to
prevent searching the same portion of the string more than once.
Here's an example to show a potential pitfall:

s = "abracadabrabracadabra"
sThis = "abracadabra"
sWithThis = "barbara"

After the first replacement,

s = "barbarabracadabra"

If you don't watch out by picking up the search after the replacement,
the second replacement would result in s = "barbarbarbara" instead of
s = "barbarabracadabra"

So not only should the 'start' argument be used to prevent searching
the same portion of string more than once for efficiency, it should
also skip over the inserted string.

How about something like:

Function Replace(ByVal s As String, ByVal sThis As String, ByVal
sWithThis As String) As String
Dim intS As Integer
Dim strTemp As String

intS = 1
strTemp = s
Do While InStr(intS, strTemp, sThis) 0
intS = InStr(intS, strTemp, sThis)
If intS = 1 Then
strTemp = sWithThis & Right(strTemp, Len(strTemp) - Len(sThis))
intS = Len(sWithThis) + 1
Else
strTemp = Mid(strTemp, 1, intS - 1) & sWithThis & Right(strTemp,
Len(strTemp) - intS - Len(sThis) + 1)
intS = intS + Len(sWithThis)
End If
Loop
Replace = strTemp
End Function

Note that if 'start' is longer than 'string1,' such as when the string
to be replaced is at the end and intS becomes one larger than the
length of strTemp, the InStr function will return 0. The built-in
Replace function of later versions of Access results, as it should, in
"barbarabracadabra" for the given example. The replacement Replace
function shown here should allow you to use the elegant technique
Douglas posted within A97 for counting the number of occurrences of a
substring within a string. Be sure to test the Replace function
thoroughly before using it and please post back if you discover any
problems with it.

James A. Fortune
CD********@FortuneJames.com