Public Function Replace2(Expression As String, Find As String, Replace As String, Optional Start As Long = 1) As String
' Author: John Barnett, handle jrbarnett
' In response to: thread700-565966 Date: 4th June 2003
' Purpose: A Find and replace VBA function for Access 97 and 2000, which didn't have the Replace() function of 2002(XP).
' although it does work in 2002 as well. It implements the mandatory functionality of the 2002 function plus the optional start, but not the Count of replacements and Binary Compare method.
' It has the same function header, so can be dropped in as a replacement.
Dim strResult As String ' variable to store result
Dim intPosition As Integer ' variable to store current position in Expression
Dim intStartPos As Integer ' variable to store current starting position within 'expression'
Dim intReplaceCount As Integer
If IsMissing(Start) Then
intStartPos = 1 ' start position not supplied; so set it to beginning
Else
intStartPos = Start ' otherwise set it and...
strResult = Left$(Expression, Start - 1) ' start by copying over first chars before start position
End If
intPosition = InStr(Expression, Find) ' locate first occurrence of 'find' data
' Remember that intPosition will = 0 if no occurrences are found.
Do While intPosition > 0
strResult = strResult & Mid$(Expression, intStartPos, (intPosition - intStartPos)) ' copy everything over from it that hasn't been copied yet
intStartPos = intPosition + Len(Find) ' increase the pointer by the length of the "to find" data so it won't find the current occurrence
strResult = strResult & Replace ' add the replacement data
intPosition = InStr(intPosition + Len(Find), Expression, Find) ' and reset the position for the new start point
Loop
' In case we aren't changing the very last part of the string...
If intStartPos < Len(Expression) Then
' copy over rest to result
strResult = strResult & Mid$(Expression, intStartPos)
End If
Replace2 = strResult ' and return a value
End Function
""