ACC: How to Remove Excess Characters from Contents of a String

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

SUMMARY

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

This article demonstrates how to use a custom function to remove excess characters from a string. This function is useful in a situation where the value in a field contains too many spaces between names or has other formatting problems.

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 create the custom function to remove excess characters from a string, follow these steps:

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

         Option Explicit
   

2. Type the following procedure:

         Function StripExtraChars (PassedStr, RemoveExtraChar$)
            On Local Error GoTo StripExtraChars_Err
            Dim i As Integer, GotChar As Integer
            Dim HoldStr As String, HoldChar As String
            ' Exit if passed value is Null.
            If IsNull(PassedStr) Then Exit Function
               ' Trim extra characters from passed string.
               PassedStr = Trim$(PassedStr)
               ' Cycle through string and remove extra
               ' string characters specified in the
               ' RemoveExtraChar value.
               For i = 1 To Len(PassedStr)
                  HoldChar = Mid$(PassedStr, i, 1)
                  If HoldChar = RemoveExtraChar Then
                     If GotChar = False Then
                        GotChar = True
                        HoldStr = HoldStr & HoldChar
                     End If
                  Else
                     GotChar = False
                  End If
                  If Not GotChar Then
                     HoldStr = HoldStr & HoldChar
                  End If
              Next i
             StripExtraChars = HoldStr
         StripExtraChars_End:
            Exit Function
         StripExtraChars_Err:
            MsgBox Error$
            Resume StripExtraChars_End
         End Function
   
   

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

         ?StripExtraChars("  My    Test    String   ", Chr$(32))
   
      Note that the string passed to the StripExtraChars() function returns
      "My Test String." The RemoveExtraChar variable passed into the function
      uses the Chr$() ASCII code equivalent of a space.
   
   

REFERENCES

For more information about string manipulation, search the Help Index for "Mid function," or "Left function," or "Right function," or "Chr function," or "Len function," or ask the Microsoft Access 97 Office Assistant.

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