Remove blank space at end of text field.

I have a text field in a table with a blank space at the end of every record.  The data before the blank varies in length.  I need to join two tables using thus field.  How can I remove this blank space?

The file is like this for what looks like all the text fields (some are numeric and date and look OK).  Can I remove thus space in all the fields at once?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would be interested in knowing how you got the space into the field.

Backup before testing this update query. (Obviously amend for your own table/field names)

Update tablename set fieldname = trim(fieldname)
vsllcAuthor Commented:
Data dump.  I used the trim function in an update query and it removed the trailing blank, but I found a few with a leading space that was not deleted.  From what I've found, trim should remove both leading and trailing.  Ideas?
Rey Obrero (Capricorn1)Commented:
yes trim removes leading and trailing spaces..
you must be having unprinted characters at the front of your field..

they could be Tab, Line Feed or New line characters

try this, if that is a Tab character

Update table
set [field]=replace([field],chr(9),"")

just to be sure of what really the unprintable character, you have to interrogate each character of field using VBA..

upload a copy of the db with the table

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

vsllcAuthor Commented:
I can't upload db.  After running, the space still exists.  His can I determine what the character is?
Rey Obrero (Capricorn1)Commented:

select all the records that have this space? and paste to a newtable
create a new db, import the newtable
upload the new  db
vsllcAuthor Commented:
Got it!  I ran the ASC function.  The char is a 160.  Thanks for the help.
Rey Obrero (Capricorn1)Commented:
now just run an update query like what i posted above

Update table
set [field]=replace([field],chr(160),"")

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.