[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Field values saved with space padding

Posted on 2004-10-31
7
Medium Priority
?
593 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
Comment
Question by:DColin
  • 3
  • 2
  • 2
7 Comments
 
LVL 12

Expert Comment

by:fulscher
ID: 12461136
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
ID: 12461160
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
ID: 12461304
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 12

Assisted Solution

by:fulscher
fulscher earned 400 total points
ID: 12461405
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:
peter57r earned 1600 total points
ID: 12461463
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
ID: 12461482
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
ID: 12461649
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Implementing simple internal controls in the Microsoft Access application.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

612 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