Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Field values saved with space padding

Posted on 2004-10-31
7
Medium Priority
?
587 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 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