Alter Table pads spaces to 255 characters

[Guest]

I have an SQL query:

ALTER TABLE [Check Import Table] ADD [Invoice Number] char, [Updated By] char

It does exactly what I want, except that it automatically fills the field
with 255 spaces. When I try running queries on these fields, I get no
results. I can't set a specific field size, because there are many different
lengths of strings being updated in those fields. How do I either turn off
the padding, or clear out the ending spaces on these fields without going
through a ton of code or queries? I know there has to be something I'm
missing. Thanks!

 

[Guest]

Try doing an update query like Trim([yourPaddedField])

 

[Conan Kelly]

Nicholas,

I looked around in Access' Help files and didn't find anything specifically, but based on my knowledge of SQL Server, "char" data
types are fixed with. Which means if you have a "char" data type with a size of 255, it will take up 255 bytes on the disk whether
or not you enter anything into them, filling empty "fields" with spaces and anything that is less than 255 characters with spaces to
reach the 255 size.

You might try this:

ALTER TABLE [Check Import Table] ADD [Invoice Number] varchar, [Updated By] varchar

"varchar" data types are variable in length, which means it only takes up the amount of space on the disk with only what was
entered. It will not fill in the rest with spaces.

Whether or not Access will behave the same way, I'm not sure.

If you want to clear out all of the fields that are filed with spaces only, you can use:

UPDATE [Check Import Table]
SET [Invoice Number] = Null
WHERE ((([Invoice Number]) Like " *"));

you can do the same for the "Updated By" field.

Anything that has a value in it, use the TRIM function in an update query, like Karl Dewey mentioned.

Remember, a zero-length string ("") is different than and empty field (NULL). Any field that should not have anything in it should
probably be set to NULL, not "". I think using the TRIM function on a field that is filled with spaces only will change it to ""
instead of NULL.

Make sure you make a backup of your table before trying any of these changes. You might have to change the "char" data types to
"varchar" before running the UPDATE queries, if the char/varchar datatypes are even the problem (I don't know if Access will
recognize the "varchar" data type--but it seems like it is recognizing "char").

I hope this helps.

Conan Kelly

 

[Guest]

I think you might be confusing the FieldSize property with the length of the
data in a text field. The column's FieldSize property will be set to 255
(the maximum for a text field) unless you specify otherwise, but in the
absence of a NOT NULL constraint the column will contain Nulls. The
FieldSize property in Access text fields merely determines the maximum number
of characters allowed. Access text fields do not contain trailing spaces,
with one exception as far as I'm aware, when the field is given an input mask
which allows text data of variable lengths.

The fact that you get no rows returned when you include these columns in a
query's WHERE clause probably arises from the fact that any comparative
operation involving a NULL evaluates to NULL not TRUE. You can test for
NULLs in a column with WHERE <column name> IS NULL, and for the converse with
WHERE <column name> IS NOT NULL. If, for example, you test for WHERE
ColumnName <> "Something" any rows with NULL in the column will not be
returned as the expression will evaluate to NULL. You could use WHERE
NZ(ColumnName,"") <> "Something", however, returning a zero length string for
any NULL so that it can then be compared with a string expression.

Ken Sheridan
Stafford, England

 

[Allen Browne]

Nicholas, the CHAR type generates a fixed-width field.

Try:
ALTER TABLE [Check Import Table] ADD [Invoice Number] TEXT (255), [Updated
By] TEXT (255)

For more info about the how the names are interpreted in DDL queries
compared to the the JET interface, DAO, and ADO, see the table in this link:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...

 

[Guest]

I had a feeling it might be something with the "char" data definition. I'm
new to using SQL queries so I just used what I saw in the example The
"text" definition solved both problems I was having. Thanks!

To Ken:
I thought that the char(XX) would set a MAXIMUM field size, but it doesn't,
it sets the field to a default size of (XX) and then pads the field with
spaces. I tried trimming the spaces out but it put them right back in.
Apparently with the "char" definition, it's (XX) size field regardless of
what you do. Maybe newer versions of Access handle this differently? (I'm on
Access 2000.)

Nicholas, the CHAR type generates a fixed-width field.

Try:
ALTER TABLE [Check Import Table] ADD [Invoice Number] TEXT (255), [Updated
By] TEXT (255)

For more info about the how the names are interpreted in DDL queries
compared to the the JET interface, DAO, and ADO, see the table in this link:
http://allenbrowne.com/ser-49.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message

I have an SQL query:

ALTER TABLE [Check Import Table] ADD [Invoice Number] char, [Updated By]
char

It does exactly what I want, except that it automatically fills the field
with 255 spaces. When I try running queries on these fields, I get no
results. I can't set a specific field size, because there are many
different
lengths of strings being updated in those fields. How do I either turn off
the padding, or clear out the ending spaces on these fields without going
through a ton of code or queries? I know there has to be something I'm
missing. Thanks!