Posted on 2006-04-19
Medium Priority
Last Modified: 2012-08-13
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.
Question by:CLSTEAM
  • 2
  • 2
LVL 26

Expert Comment

ID: 16490025
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.  

LVL 13

Expert Comment

ID: 16490212
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.

Author Comment

ID: 16490655
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!

LVL 26

Accepted Solution

jerryb30 earned 150 total points
ID: 16490684
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.

Author Comment

ID: 16490710
Ok - just wanted to see if I was missing something.  Thanks so much.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

750 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