why is Access space-padding my column?

[jal]

I have an AcctNo field called AcctToUse (it's actually datatype Text, 50 chars long). I created this field in (table) Design view. It's default value is empty string.

In VBA I set this value to -1 all the way down the column.

DoCmd.RunSQL "UPDATE Posted SET AcctToUse = '-1' "

Later on an INNER JOIN is returning zero records because of failing WHERE clause

WHERE Posted.AcctToUse = '-1'

This shouldn't fail because, looking at the table, all the values are indeed -1. But then I looked a little closer. The value -1 is space-padded to a length of 50 so that it's real value is something like this (I'll use an underline here as a fake space)

'-1____________________________________'

And I verified this by doing a query:

SELECT AcctToUse, Len(AcctToUse) FROM Posted
This returns a length of 50 all the way down.

Why the padding?

 

[Banana]

In my case when I used MySQL backend, using CHAR datatype causes the data to be of a fixed length and thus padded with spaces. Now, CHAR is supposed to be trimmed prior to displaying on Access form, (as I understand is the case with MS SQL backend) but in this case, it wasn't. Changing the datatype to VARCHAR will work around the problem, though there are performance ramifications. (In my case, I was using InnoDB so that wasn't a big deal, but if I were using MyISAM which works much faster with fixed lengths enabling the indexes to be multiples of bytes consumed per row, this would be bad)

Also, the driver may affect the presentation of data; check its documentation and see what it says about padding and whether it can be enabled/disabled.

HTH.

 

[jal]

In my case when I used MySQL backend, using CHAR datatype causes the data to be of a fixed length and thus padded with spaces. Now, CHAR is supposed to be trimmed prior to displaying on Access form, (as I understand is the case with MS SQL backend) but in this case, it wasn't. Changing the datatype to VARCHAR will work around the problem, though there are performance ramifications. (In my case, I was using InnoDB so that wasn't a big deal, but if I were using MyISAM which works much faster with fixed lengths enabling the indexes to be multiples of bytes consumed per row, this would be bad)

Also, the driver may affect the presentation of data; check its documentation and see what it says about padding and whether it can be enabled/disabled.

HTH.

I'm not using a backend. I'm just using Access 2003. In Access I don't see a "Char" datatype or a "varChar" datatype. I don't see those options. All I have is "Text" and 'Memo" and I used "Text".

 

[Banana]

Wow. Stumped me now as I had assumed that it was a backend datatype mapping problem. Never seen that problem in a pure Access solution.

To be clear, is the database split?

Have you already done a Compact & Repair?

Does it makes any difference if the update command used "" instead if '' for the -1? (I don't think so, but want to cover all bases)

 

[jal]

Wow. Stumped me now as I had assumed that it was a backend datatype mapping problem. Never seen that problem in a pure Access solution.

To be clear, is the database split?

Have you already done a Compact & Repair?

Does it makes any difference if the update command used "" instead if '' for the -1? (I don't think so, but want to cover all bases)

I have my DB set to Compact automatically at shutdown so I am sure it was compacted several times.

As for embedding double quotes into this line:

DoCmd.RunSQL "UPDATE Posted SET AcctToUse = '-1' "

I can't even recall how to do it (I guess I'd have to use a string of three or four quotes in a row). But since then, out of desperation, I removed the column from my table and then recreated it, in Design view. The problem disappeared.

Perhaps I had earlier enabled some kind of fixed-length setting on that column without realizing it, but I didn't find such a setting when I looked at it in Design view.

No, the database isn't split. This is about as simple a DB as it gets.

 

[gemma-the-husky]

jal

irrespective of the corrent issue, regarding text length,
i cant understand why you are setting a text field to -1

if you want a flag (-1 is true) then just have a boolean yes/no value, surely

 

[LPurvis]

I agree that the '-1' looks suspicious.
I suppose if we look at the fieldname "AcctToUse" we could give you the benefit of the doubt in that it might normally hold an account number - but in some eventuality you want a generic exempting value (like a "N/A")?
If this is the case - please feel free to say so. It'll make us feel better. :-D

As to the fixed length issue - it's by no means certain how you achieved it - but Jet is perfectly capable of creating and using Char fields. (They're just not offered through the Access UI - but that isn't the only of Jet's capabilities that isn't reflected there). They still appear in the Table Designer as "Text" though - therein the confusion.

Just change the datatype if it's not what you want.
Bear in mind that, even after changing type, you'll likely have to then trim the contents of existing entries (but this will be a one time deal).

Of course - changing datatype of a field is something DAO can't help you with (well - not via the TDF object model anyway).
i.e.
CurrentDb("Posted")("AcctToUse").Type = dbText
will tell you where to go.

A DDL statement will allow it though
CurrentDb.Execute "ALTER TABLE Posted ALTER COLUMN AcctToUse varChar(" & CurrentDb("Posted")("AcctToUse").Size & ")"

And then that trimming
CurrentDb.Execute "UPDATE Posted SET AcctToUse = Trim(AcctToUse) WHERE AcctToUse Is Not Null"

Cheers.

 

[jal]

jal

irrespective of the corrent issue, regarding text length,
i cant understand why you are setting a text field to -1

if you want a flag (-1 is true) then just have a boolean yes/no value, surely

If it's a text field, it shouldn't really matter what text I put in here - it could be an excerpt of the U.S. Declaration of Independence for heaven's sake. Therefore I'm strongly inclined to disagree that "-1" is suspicious. And, as I said, I'm still using the -1 and it now works fine. All I did to fix it was delete the column and reinstate it - I didn't change one word of my VBA code.

But if you really want to know why I did it this way:

The application is reading in a Microsoft Word document which is basically a series of invoices. The data isn't arranged in "fields" of any kind which makes the parsing difficult. All I have to go on is the relative position of the words to figure out what is what, when parsing. The parsing has to determine the customer name, account number, etc. Some account numbers have letters, so I used a text field for acct number. However, none of the account numbers begin with a negative sign. So I initially set the acct number to -1 indicating that the acct number isn't yet populated - because not all of them will be populated (some invoices are missing account numbers)

Why not set it to empty string? Because there are actually two tables involved (long story). For the other table, i needed to use a different flag for missing account numbers, in certain queries that compare the two tables. The simplest way was to use "-1" in one table and "0" in another table. I haven't got time to go into all the details of this situation, however.

 

[WayneRyan]

jal,

I also haven't seen Access right-pad with spaces. Dealing with SQL Server
and ADO, I always see it.

One quick solution:

WHERE RTrim(Posted.AcctToUse) = '-1'

To detect it:

NewColumn: "[" & Posted.AcctToUse & "]"

Or:

MsgBox "[" & Posted.AcctToUse & "]"

hth,
Wayne

 

[Banana]

WayneRyan, does the padding happen if using Access with ADO as well?

 

[WayneRyan]

Banana,

I see it quite frequently when using ADO to connect to SQL Server. Which
particular SQL datatypes I don't know offhand, but I'd venture that they are
declared as Varchar(50) and such.

If you use a recordset value to generate some dynamic SQL, I generally have
an RTrim in there somewhere.

Wayne

 

[Banana]

Afraid my question was horribly phrased. I meant if a query was executed against an Access backend using ADO rather than DAO, does the padding show up?

It's interesting that you get padding with VARCHAR for SQL server as I usually use VARCHAR on MySQL to avoid the padding (CHAR gives me the padding).

 

[WayneRyan]

Banana,

Sorry, I don't apply any ADO against Access back ends. There are so many
databases and different table schemas at work, it's all a blur, but I'm pretty
sure that with ADO, even the Varchars are padded.

Wayne

 

[Banana]

Interesting.

Thanks!

 

[LPurvis]

Jal - it was just a question. And no doubt one intended with your best interests in mind. The explanation you grudgingly offer had subsequently been hypothesized as the reason.

I'd already offered a reason why it was exhibiting the behaviour you described.
Given the field's been destroyed now there's no way of verifying the suggestion.
But I'd wager it too would have worked.

For this behaviour encountered against a SQL Server db I'd wager that it's still just Char fields again. Accessed through ADO or not - varChars don't pad (by definition)... Chars do. (The padding manifests as space characters - though can be the ASCII Null char on imported data - though don't get me started on how much I dislike that character's inaccurate name).