Solved

Trim function does not always work in Access 2003

Posted on 2012-03-09
5
576 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms access 2010 vba, copy table from db1 to db2 from db3 27 46
Passing variables to a function 6 35
Column Layout in Access Xp VBA Report 3 19
combo box query 6 6
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

773 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