Solved

Field values saved with space padding

Posted on 2004-10-31
577 Views
Last Modified: 2008-03-17
I have an Access 2000 database holding subscription IDs for my customers. The IDs range from a single digit e.g. "0" to multiple digits e.g. "1234567890", the field properties are 50 chars of text. I have just discovered that some of the fields only contain the subscription ID but some others contain the subscription ID plus 'space' padding up to 50 char length. Is there something obvious that I am doing wrong to cause this and more importantly how do I remove all these unwanted space chars padding the fields of my database. Thanks.
0
Question by:DColin
    7 Comments
     
    LVL 12

    Expert Comment

    by:fulscher
    The form editor ususally would save the strings with their effective size. Where do your get the IDs from? If you import them from somewhere else, you might need to strip padding spaces. Do the users use COPY/PASTE from some other data source?

    For stripping the unwanted spaces:
    - in the current data: Use an table update query and the TRIM$ function for the ID field
    - in new data: depends a bit how the strings come from their source. Anyway, you could use TRIM to strip spaces in the BeforeUpate event of the field of the form.

    HTH, J
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Hello DColin,

    It is not possible to create 'padded' fields through the UI, unless you force the entry through an input mask.
    However, if the field is created through an SQL statement or in DAO code it is possible to specify a fixed length field, which would then pad out with the necessary spaces.
    Having said this I can't see how you can have a mixture of formats in the same field.
    Was the data entered through a form or was it imported?


    Pete
    0
     

    Author Comment

    by:DColin
    fulscher & peter57r

    I have found out that colums created using the Access GUI client are OK but columns add using the following SQL query contain the 'space' padding:

    SQLStr = "ALTER TABLE " & Request("TableName") & " ADD COLUMN " & Request("FieldName") & " CHAR(50) ;"

    How can I modify this SQL statement not to pad the field and how do I run a TRIM$ table update query? Thanks.
    0
     
    LVL 12

    Assisted Solution

    by:fulscher
    DColin,

    The query you mentioned is - AFAIK - used to add a column to a table; this column would have width 50 characters. There probably is another query somewhere which actually adds or modifies records. Could you check this, please?

    Update query to trim spaces in access: Go to query designer, create new query, switch to SQL view and add the following SQL statement:

    UPDATE <tablename> SET <fieldname> = Trim([<fieldname>]);

    Replace <tablename> with the name of the table where the IDs are stored in (the result should NOT contain angle brackets). Replace <fieldname> with the name of the field the user ID is stored in (again, the result should not contain angle brackets). The result should look similar to the following:
        UPDATE tPaddingTest SET Field1 = Trim([Field1]);

    HTH, J
    0
     
    LVL 77

    Accepted Solution

    by:
    Once you have created the column with Char(50) the data cannot be trimmed.  Char(50) creates a fixed length field.
    As I said before, you cannot create this type of field through the Access GUI - only in SQL or code.

    You need to create another column using Text(50) - which is the equivalent of the Access GUI text datatype- and this will fix future entries.  
    You can then use an update query on the new field to update values to 'trim(fieldname)'

    Pete
    0
     
    LVL 77

    Expert Comment

    by:peter57r
    Sorry - I didn't mean to say that you MUST create the new field in SQL; you can just create it through the Access table design GUI.  
    But as far as I know you cannot UNSET the fixed length setting on the existing field except through SQL or code.

    Pete
    0
     

    Author Comment

    by:DColin
    Thanks for your help you both gave me a good understanding of the problem and how to avoid it in the future. I have been very lucky with fixing my existing database. The subscription IDs are very long but consist of numeric chars only, so I changed the column to a double precission number and then changed it back to a Text(50) column and 'hey presto' problem solved, no padding.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    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…
    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…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    884 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

    18 Experts available now in Live!

    Get 1:1 Help Now