ACC: How to Replace a String with Another String

MORE INFORMATION

• the string that contains the string you want to replace.
• the specific string to be replaced.
• the value of the replacement string.

1. Create a new module.
2. Type the following code.
   
   NOTE: In the following sample code, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

        '=============================================================
         ' Declarations section of the module
         '=============================================================
         Option Explicit
   
         Function Replace(ByVal Valuein As String, ByVal WhatToReplace As _
                          String, ByVal Replacevalue As String) As String
            Dim Temp as String, P As Long
            Temp = Valuein
            P = Instr(Temp, WhatToReplace)
            Do While P > 0
               Temp=Left(Temp, P-1) & Replacevalue & _
                   Mid(Temp, P+Len(WhatToReplace))
               P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
            Loop
            Replace = Temp
         End Function
   						

3. To replace the string "Sample" with the string "Test" in the string "This is a Sample," use the following parameters for the Replace() function:

         Valuein: "This is a Sample"
         WhatToReplace: "Sample"
         ReplaceValue: "Test"
   						

   Note that the function Replace("This is a Sample","Sample","Test") returns:
   This is a Test