ACC: How to Strip Specific Characters from a String

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q138774

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

Expand all | Collapse all

SUMMARY

This article shows you how to create a custom function to strip invalid characters, or characters that you do not want, 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

Back to the top

MORE INFORMATION

To strip invalid characters, or characters that you do not want, from a string, follow these steps:

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

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

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.

Back to the top

REFERENCES

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

Back to the top

APPLIES TO

Microsoft Access 1.0 Standard Edition
Microsoft Access 1.1 Standard Edition
Microsoft Access 2.0 Standard Edition
Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

Back to the top

kbhowto kbprogramming KB138774

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top