ACC2000: How to Strip Extra Characters from a String

• Microsoft Access 2000

SUMMARY

This article shows you how to create a custom function to strip extra 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.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

MORE INFORMATION

To create the custom function to strip extra 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 Immediate window, 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 the Chr() function and character codes, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Chr" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Additional query words:

Keywords : kbusage kbdta AccCon KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto