Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Modules (VBA Coding) FAQ

E-mail Attached Files

How do I replace some text in as string with some other text string? by overClok6
Posted: 7 May 01

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
        Position = InStr(1, SourceString, OriginalString)
        If Position > 0 Then
            ReplaceString = (Mid$(SourceString, 1, Position - 1) & NewString & ReplaceString(Mid(SourceString, Position + Len(OriginalString)), OriginalString, NewString))
            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.

Back to Microsoft: Access Modules (VBA Coding) FAQ Index
Back to Microsoft: Access Modules (VBA Coding) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close