This module will trim leading and trailing spaces in a field, as well as extra spaces between words. For example, it will turn "(space) Tom (space)(space)(space)McGinty(space)." into "Tom(space)McGinty".

To find the Microsoft article about this module, go to www.microsoft.com/directory and click on "Searchable Knowledge Database." Search for story number Q148396. From there you can copy and paste this module directly into Access.

Here's how the module works: Go to the module tab on your database window and click on the "new" button. The window that pops up should have the following two lines at the top: Option Compare Database Option Explicit If it does not, add them. Next, type the following syntax below those two lines:

Function StripExtraChars (PassedStr, RemoveExtraChar$) On Local Error GoTo stripExtraChars_Em 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

Next, under the "Debug" menu, choose "Compile and save all modules." It will prompt you to name the module. Name it "TrimSpaces" and then close the module.

Now, let's say you have a field called NAME that has extra spaces between first and last names, and unwanted spaces at the beginning and end of some of the names First, create a new field in your table called "NEWNAME"(NEVER change your original data).

Then start a new query, add your table to the query and then, under the "Query" menu, choose "Update Query." Pull the "NEWNAME" field down into the "Field" row of the update query. Then type this in the "Update To:" row of the query:

StripExtraChars([NAME],Chr$(32))

That applies the new module you just created to the NAME field and puts the results into the NEWNAME field. The second argument of the function, Chr$(32)), is the ASCII code for spaces. The SQL for the same update query would look like this: update TABLENAME set NEWNAME = StripExtraChars([NAME],Chr$(32)) Tom McGinty can be reached by e-mail at tmcginty@nicar.org