changing blanks to null

[William Kossack]

I have an Access table with one primary key and am attempting to update
a non-key field, using

UPDATE tblMethtest SET fev1timemeth = '' WHERE SID = '0041R';

When I do this, the field subsequently contains 5 blanks instead of the
zero-length string I tried to put into it. This problem occurs
regardless of whether I execute the SQL from within Cold Fusion or from
within Access as an Access query. The same problem occurs on any
non-key text field in the table, ie, the field gets filled with blanks
to its defined length.

The field is defined as follows, according to Access's Documenter:

fev1timemeth Text 5
AllowZeroLength: True
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
DisplayControl: Text Box
GUID: Long binary data
Ordinal Position: 52
Required: False
Source Field: fev1timemeth

 

[KirSol]

I have an Access table with one primary key and am attempting to update
a non-key field, using

UPDATE tblMethtest SET fev1timemeth = '' WHERE SID = '0041R';

When I do this, the field subsequently contains 5 blanks instead of the
zero-length string I tried to put into it. This problem occurs
regardless of whether I execute the SQL from within Cold Fusion or from
within Access as an Access query. The same problem occurs on any
non-key text field in the table, ie, the field gets filled with blanks
to its defined length.

The field is defined as follows, according to Access's Documenter:

fev1timemeth Text 5
AllowZeroLength: True
Attributes: Fixed Size
Collating Order: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
Data Updatable: False
DisplayControl: Text Box
GUID: Long binary data
Ordinal Position: 52
Required: False
Source Field: fev1timemeth

The "Fixed Size" field attribute is the cause of the behavior. You'll need
to use code to modify it:

CurrentDb.Execute "ALTER TABLE tblMethtest ALTER COLUMN fev1timemeth
TEXT(5);"

In the above statement I set the length to 5, given that's what it appears
to be now.

Of course, I'm assuming your database won't suffer other adverse
consequences from making this change, and obviously make a backup first!

Technical explanation: Variable length text fields are the Access default,
and they can't be changed via the Access table designer. So your field must
have gotten modified at some point in its history either via an Access DDL
statement (CREATE TABLE or ALTER TABLE), or through Jet/DAO manipulation
using the TableDef and Field objects and the dbFixedField constant). It's
not possible to modify this attribute via the Access table designer, hence
the need to use code to modify it. If you were ever going to change it from
variable, to fixed, you would substitute CHAR(5) instead of TEXT(5) in the
above statement.

Mitch

 

[John Vinson]

fev1timemeth Text 5
AllowZeroLength: True
Attributes: Fixed Size

Ok, you say it's a field of length 5 and that it's Fixed Size.

That implies to me that it cannot contain a string of length twenty,
or 2... or 0, since zero is not equal to 5.


If you want it to be NULL, just set it to NULL rather than to ''.