Trim function does not always work in Access 2003
Posted on 2012-03-09
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?