INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

*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.

Jobs

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
    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.

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

My Archive

Resources

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