By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,828 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Unwanted trailing spaces added to text column

P: n/a
ITM
Can anyone tell me how I can prevent Access adding trailing spaces
when I insert a value into a Text column?

For example, if I execute the following statement:

UPDATE Log SET Log.Title = 'Testing'
WHERE Log.Identifier=1;

....then the following statement:

SELECT Len(Log.Title) FROM Log
WHERE Log.Identifier=1;

The Second statement returns 100 (this column has a size of 100). I
was expecting it to return 7.

Since I didn't include trailing spaces in my SQL UPDATE statement, why
is Access inserting them, and how can I stop it from doing so?

TIA for any tips
Isabel

PS - I'm using MS-Access 2003, but have observed the same problem on
Access
2000.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"ITM" <it*@nospam.com> wrote in message
news:mm********************************@4ax.com...
Can anyone tell me how I can prevent Access adding trailing spaces
when I insert a value into a Text column?

For example, if I execute the following statement:

UPDATE Log SET Log.Title = 'Testing'
WHERE Log.Identifier=1;

...then the following statement:

SELECT Len(Log.Title) FROM Log
WHERE Log.Identifier=1;

The Second statement returns 100 (this column has a size of 100). I
was expecting it to return 7.

Since I didn't include trailing spaces in my SQL UPDATE statement, why
is Access inserting them, and how can I stop it from doing so?


Are you using a Jet table or an ODBC linked table to some server back end?
If Jet, did you create the tables the standard way or did you use CREATE
TABLE statements? While the normal Text fields in Jet do not pad spaces
you can define a field with DDL as CHAR and then it will pad spaces to the
limit of the size specified. If it's a link to a server back end some
RDBMS' will pad spaces depending on the field type.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #2

P: n/a
ITM
On Wed, 21 Jan 2004 12:13:59 -0600, "Rick Brandt"
<ri*********@hotmail.com> wrote:
Are you using a Jet table or an ODBC linked table to some server back end?
If Jet, did you create the tables the standard way or did you use CREATE
TABLE statements? While the normal Text fields in Jet do not pad spaces
you can define a field with DDL as CHAR and then it will pad spaces to the
limit of the size specified. If it's a link to a server back end some
RDBMS' will pad spaces depending on the field type.


Thanks for your reply Rick.

The table is a Jet table, created the standard way. I'm puzzled as to
why it's padding in this way!

I.
Nov 12 '05 #3

P: n/a
"ITM" <it*@nospam.com> wrote in message
news:gv********************************@4ax.com...
On Wed, 21 Jan 2004 12:13:59 -0600, "Rick Brandt"
<ri*********@hotmail.com> wrote:
Are you using a Jet table or an ODBC linked table to some server back end?
If Jet, did you create the tables the standard way or did you use CREATE
TABLE statements? While the normal Text fields in Jet do not pad spaces
you can define a field with DDL as CHAR and then it will pad spaces to the
limit of the size specified. If it's a link to a server back end some
RDBMS' will pad spaces depending on the field type.


Thanks for your reply Rick.

The table is a Jet table, created the standard way. I'm puzzled as to
why it's padding in this way!


Do you see the padding when viewing the table directly or are you just going by
the response of the Len() function? If the table is actually getting padded you
will be able to tell by how the cursor responds in the field.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #4

P: n/a
ITM
>> >
Unfortunately in Access I don't have a "varchar" option - only "Text".
Is there any way that I can avoid this automatic padding in "Text"
columns?


Access Text IS VarChar. Did you see my previous response? You need to examine
the table to verify if the entries are in fact being padded. When you tab into
the field Access will highlight the entire contents. If it is padded the
highlighting will include the padded spaces. If the highlighting stops where
the characters stop then there is no padding and something else is causing your
erroneous Len() value.


Yes - as I mentioned in my previous post, when I drag my cursor over
the field to select the content it highlights trailing spaces after
the data content

??
Nov 12 '05 #5

P: n/a
"ITM" <it*@nospam.com> wrote in message
news:e2********************************@4ax.com...
>
Unfortunately in Access I don't have a "varchar" option - only "Text".
Is there any way that I can avoid this automatic padding in "Text"
columns?


Access Text IS VarChar. Did you see my previous response? You need to examinethe table to verify if the entries are in fact being padded. When you tab intothe field Access will highlight the entire contents. If it is padded the
highlighting will include the padded spaces. If the highlighting stops where
the characters stop then there is no padding and something else is causing yourerroneous Len() value.


Yes - as I mentioned in my previous post, when I drag my cursor over
the field to select the content it highlights trailing spaces after
the data content


Does this only happen on the rows where your query inserted data or do all of
the rows have padding? I believe I have seen cases where I copied data from a
table that padded spaces into another table and the padding "came along" with
the text, but I can't see how your INSERT statement would do this.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #6

P: n/a
ITM
On Thu, 22 Jan 2004 16:29:50 -0600, "Rick Brandt"
<ri*********@hotmail.com> wrote:
Does this only happen on the rows where your query inserted data or do all of
the rows have padding? I believe I have seen cases where I copied data from a
table that padded spaces into another table and the padding "came along" with
the text, but I can't see how your INSERT statement would do this.


All of the rows have padding.

Having investigated further, it seems that it's possible that this
table may have been imported from another (MS-Access) database in the
past (the database has been around for several years). I wonder if
this holds the key to my problem?

If I create a new table in this database I don't get the problem.
Hence I can resolve this specific issue by recreating the table
manually and importing/truncating data from the old table - but I'd
like to know the original cause so that it doesn't occur again!
Nov 12 '05 #7

P: n/a
"ITM" <it*@nospam.com> wrote in message
news:dp********************************@4ax.com...
On Thu, 22 Jan 2004 16:29:50 -0600, "Rick Brandt"
<ri*********@hotmail.com> wrote:
Does this only happen on the rows where your query inserted data or do all ofthe rows have padding? I believe I have seen cases where I copied data from atable that padded spaces into another table and the padding "came along" withthe text, but I can't see how your INSERT statement would do this.


All of the rows have padding.

Having investigated further, it seems that it's possible that this
table may have been imported from another (MS-Access) database in the
past (the database has been around for several years). I wonder if
this holds the key to my problem?

If I create a new table in this database I don't get the problem.
Hence I can resolve this specific issue by recreating the table
manually and importing/truncating data from the old table - but I'd
like to know the original cause so that it doesn't occur again!


I just did a test. If I create a link to our ISeries to a table that uses
space padding and then feed that into a MakeTable query the resulting local
table also has the spaces padded. However, this is only true with the
original data. If I add a row (manually or with a query) I do not get
space padding and if I edit an existing entry with space padding the new
entry does not have padding.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.