Hi there I have serveral tables that come from a SQL server.  When the tables are linked to Access most of the fields have spaces before and after the data.  In each table there is just too many fields to use the trim([field name]) on all of them.  Is there a function that can update all the fields at once?  Also is there a way that when the SQL tables are import or linked that the fields are automatically trimmed without having to do this if the trim all fields is not available.  Thank you so much.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

If you have imported the tables into Access, it would be fairly simple to write a function to trim existing data in existing fierlds in all tables.  Do not know if this is do-able (or wise) for data in tables in the SQL Server db.  

LucasMS Dynamics DeveloperCommented:
If you want to remove the spaces, you have to change the data type on the SQL server side.  I'm not sure what it is, but it might by nVarChar or VarChar.  

I have the same problem, but it shouldn't matter when you generate queries otherwise you'll have to use the TRIM function in your query.
CLSTEAMAuthor Commented:
Ok I understand but is there a function that does a Trim([Table*])  

If I do this then I get a message that you cannot update using *

I think that it is a great tool but having to do each one, one by one by one!

There is not an existing function within Access for this.  It is doable, if you have imported the tables, creating your own function.  It may seem slow, depending on how many tables, fields, records.

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
CLSTEAMAuthor Commented:
Ok - just wanted to see if I was missing something.  Thanks so much.
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.