?
Solved

Trim function does not always work in Access 2003

Posted on 2012-03-09
5
Medium Priority
?
627 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:Liberty4all
  • 2
  • 2
5 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1336 total points
ID: 37703835
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
 

Author Comment

by:Liberty4all
ID: 37703953
The above statement returns a value of 160 for each record.  What does this mean?
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 664 total points
ID: 37703986
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
 
LVL 61

Accepted Solution

by:
mbizup earned 1336 total points
ID: 37703993
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
 

Author Closing Comment

by:Liberty4all
ID: 37704080
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question