Trim function does not always work in Access 2003

I imported an Excel file into an Access table and noticed the ZipCode field has leading spaces in it.  I tried using an update query to eliminate these but was not successful.  The Update To row of my query reads Trim([ZipCode]).

I tried this same query on another table and it works fine so I'm guessing the problem is with the data.  The column in Excel is formatted as general and no indentation is specified.  The Excel source file has over 1,000 records and will be received frequently.  It's too time consuming to manually eliminate each leading space for every record and unless it's possible to quickly fix it with a formatting change I will need to do whatever is necessary in Access to compensate.  

Can someone please suggest another approach to eliminate leading spaces either in Excel or Access?
Liberty4allAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Hi -

That is the ascii character code for your first character.  This chart lists character codes:
http://www.asciitable.com/

A space is ascii 32.

Your character is not a space -- it is some other control character that looks like a space (which is why TRIM won't work).

If it appears consistently there in all records, try this to get rid of it:

     Right([ZipCode], Len([ZipCode] - 1))

Another (better) option is:

     Replace([ZipCode],chr(160),"")

Try those in a SELECT query first.  If it looks like it works, you can make a backup and change the query to an UPDATE query to actually change the data.
0
 
mbizupConnect With a Mentor Commented:
Its possible that the leading space is not a space, but some other blank character.

Try this in a select statement in access to determine what the character code of the first character is:

ASC(Left([ZipCode],1))
0
 
Liberty4allAuthor Commented:
The above statement returns a value of 160 for each record.  What does this mean?
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
that means you got a character, not a space at the start of your ZipCode field

see this link for the Ascii values and equivalent characters

http://www.asciitable.com/

to get rid of the first character, you can run an update query

update tableName
set [zipCode]=mid([zipCode],2)
where asc(left([zipcode],1))=160
0
 
Liberty4allAuthor Commented:
Thank you both for very quick responses.   Replace([ZipCode],chr(160),"") works best with my current data.  I would not have been able top solve this without your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.