ACC: How to Strip Specific Characters from a String

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This articles describes how to use a custom function to remove invalid or unwanted characters from a string. You can use this function in queries and in text input boxes.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

MORE INFORMATION

To remove invalid or unwanted characters from a string, follow these steps:

1. Create a new module and type the following line in the Declarations section if it's not already there:

         Option Explicit
   

2. Type the following procedure:

         ' --------------------------------------------------
         ' Function StripString()
         '
         ' Returns a string minus a set of specified chars.
         ' --------------------------------------------------
         Function StripString(MyStr As Variant) As Variant
            On Error GoTo StripStringError
   
            Dim strChar As String, strHoldString As String
            Dim i As Integer
   
            ' Exit if the passed value is null.
            If IsNull(MyStr) Then Exit Function
   
            ' Exit if the passed value is not a string.
            If VarType(MyStr) <> 8 Then Exit Function
   
            ' Check each value for invalid characters.
            For i = 1 To Len(MyStr)
               strChar = Mid$(MyStr, i, 1)
               Select Case strChar
                  Case ".", "#", ",", "-"
                     ' Do nothing
                  Case Else
                     strHoldString = strHoldString & strChar
               End Select
            Next i
   
            ' Pass back corrected string.
            StripString = strHoldString
   
         StripStringEnd:
            Exit Function
   
         StripStringError:
            MsgBox Error$
            Resume StripStringEnd
         End Function
   
   

3. To test this function, type the following line in the Debug window (or Immediate window in versions 1.x or 2.0), and then press ENTER.

          ?StripString("Test.#,-")
   

   Note that the function prints the word "Test" in the Debug window. The invalid characters (".#,-") are successfully stripped from the test string.

REFERENCES

For more information about string manipulation, search in the Help Index for "strings, manipulation."

Keywords          : kbprg PgmHowTo PgmParse
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto