Solved

Trim function does not always work in Access 2003

Posted on 2012-03-09
5
610 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
[X]
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
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

617 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