Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Trim function does not always work in Access 2003

Posted on 2012-03-09
5
583 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 334 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 166 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 334 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

792 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