Field values saved with space padding

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.
DColinAsked:
Who is Participating?
 
peter57rCommented:
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
 
fulscherCommented:
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
 
peter57rCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DColinAuthor Commented:
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
 
fulscherCommented:
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
 
peter57rCommented:
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
 
DColinAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.