Solved

How to trim spaces in captured data on some fields ?

Posted on 2009-07-15
8
294 Views
Last Modified: 2012-05-07
HI

We have imported some data to an access table but it seems that some fields have some spaces at right and left side.

I wonder if there´s an access function that could trim those spaces.

Thanks in advance.
0
Comment
Question by:dimensionav
  • 5
8 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 24866352

Trim(yourFieldName)

mx
0
 
LVL 4

Assisted Solution

by:mysteriousguy
mysteriousguy earned 250 total points
ID: 24866353
There are 3 functions doing this.
LTrim trims the left spaces
RTrim trims the right spaces
Trim trims left and right spaces

So you could update yout table like this:
UPDATE table set column = Trim(column)

http://www.techonthenet.com/access/functions/string/trim.php
http://www.techonthenet.com/access/functions/string/ltrim.php
http://www.techonthenet.com/access/functions/string/rtrim.php
0
 
LVL 75
ID: 24866363
For example ... using an Update query:

UPDATE Table1 SET Table1.FIELD1 = Trim([FIELD1]), Table1.FIELD2 = Trim([FIELD2]), Table1.FIELD3 = Trim([FIELD3]);


mx
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 75
ID: 24866457
I posted the correct answer first - followed by an example.

mx
0
 

Author Comment

by:dimensionav
ID: 24866500
I´m very sorry DatabaseMX, I just saw the mysteriousguy example first, I haven´t noticed your next example after trying mysteriousguy one.

What would you like I do ?
Regards
0
 
LVL 75
ID: 24866506
Click the Request Attention button ... the mods will help you.

thx
0
 
LVL 75
ID: 24906447
thx.
Appreciate this.

mx
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

856 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