Posted on 2006-04-19
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
    LVL 28

    Expert Comment

    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

    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

    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 28

    Accepted Solution

    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

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

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    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…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    754 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

    24 Experts available now in Live!

    Get 1:1 Help Now