Solved

How to trim spaces in captured data on some fields ?

Posted on 2009-07-15
8
291 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 - Access MVP) 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now