How do I replace some text in as string with some other text string?

You can use this function that will actually replace the characters within a specified string with some other characters. This is MUCH better than using the MID statement because it doesn't care about the length of the string you are replacing. You can also use it to remove a group of characters from a string.

Function ReplaceString(ByVal SourceString As String, ByVal OriginalString As String, ByVal NewString As String) As String

'Recursive function ReplaceString searches Source string and replaces ALL OCCURRENCES of OriginalString with NewString.
'If a value for NewString is ommitted (or IsEmpty), then all occurrences of OriginalString are removed from the SourceString!

Dim Position As Integer

If SourceString = "" Or IsNull(SourceString) Then
ReplaceString = SourceString
Else
Position = InStr(1, SourceString, OriginalString)
If Position > 0 Then
ReplaceString = (Mid$(SourceString, 1, Position - 1) & NewString & ReplaceString(Mid(SourceString, Position + Len(OriginalString)), OriginalString, NewString))
Else
ReplaceString = SourceString
End If
End If

End Function


You may use this function in your native SQL statements or in your VB code. Just copy this function code to your module.