Table design changes

[Paul Johnson]

I recently added a field to an existing table, and shortly began receiving
"Field '(fieldName)' cannot be a zero-length string" error messages for
various fields in the table when appending the table in DAO. I went back to
look at the table design, and discovered that *all* the text fields in the
table had been changed to disallow zero-length strings, though they had
previously allowed them.

As far as I can tell, this change coincided with the addition of the one new
field to the table. Why did this affect all the other text fields? Is it a
bug, or is there a default option that was globally applied to the table
without my knowing it?

I am running AC2000 on Win2000. I wouldn't think this would make any
difference, but the table design was imported into the database from an
Access97 database, so it was originally designed in dbVersion30.

Thanks in advance for any insight.

Paul

 

[Allen Browne]

In A97, when you create a text field, the Allow Zero Length (AZL) property
is set to No by default. That's true whether the field is created through
the interface or via DAO.

In A2002 and 2003, the AZL is set to Yes if you create through the
interface, No if you create the field with DAO, and who knows what you will
get if you create a field with DDL or ADOX. MS really stuffed this up. The
only way they can sort out the inconsistencies that now exist between
versions and creation methods is if they provide a configuration option
where you can set the default you want.

While the AZL settting for a new field will depend on how you added it and
in what version, that should not affect all the other existing fields in
your table. If they really have changed, then you have some kind of
corruption at work, probably the result of Name AutoCorrect losing track of
things. More information:
Failures caused by Name Auto-Correct
at:
http://members.iinet.net.au/~allenbrowne/bug-03.html

BTW, having AZL on makes no sense for most fields. Users are guaranteed to
get confused between a Null and a Zero Length String (ZLS),not to mention
developers. Access itself can't tell the difference properly: DLookup()
wrongly reports Null for a ZLS.

 

[Paul Johnson]

My "Track name AutoCorrect info" box was checked, so it may have been the
culprit. I have cleared it. You make another point that raises new
questions for me, however (below).

BTW, having AZL on makes no sense for most fields. Users are guaranteed to
get confused between a Null and a Zero Length String (ZLS),not to mention
developers. Access itself can't tell the difference properly: DLookup()
wrongly reports Null for a ZLS.

* I wouldn't think I would need to have this on, except that after the AZL
field properties changed, my application was throwing an error message when
it encountered a field that (I thought) contained Null. Based on this
behavior, I figured that Access did not distinguish between the two. This
concept is new to me; I always figured that if the user entered nothing, or
deleted the contents of a text field, then the field would be Null, not a
ZLS.

I have run the following expression in a query to analyze the contents of
one of the affected fields:

IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;""ZLS"""))

I am surprised to see that in my table of 241 records, there are 157 Nulls
and 47 ZLSs, the other 37 contain text entries. It is easy enough to change
a ZLS to a null: I can either put a space character in the empty field
(Access must interpret this as an entry of 'nothing' into the field and
convert it to a null value), or I can enter text and delete it. I see that
if I try to enter a pair of quotes (""), I get the error message "Field
can't allow zero-length string." What I can't figure out is how those
fields became ZLSs in the first place. I don't imagine there were any users
entering double quotes in the field when they wanted it blank!

If I can't figure out how my table became populated with ZLSs, I will need
to use the AZL property in the table to avoid error messages. I could
always use error-trapping, and "Resume Next" on that specific error, but I
get nervous when I push past Access' warnings that way.

Any other insights?

TIA
Paul

 

[Allen Browne]

Hi Paul.

Yes, the ZLS is way too confusing and unnecessarily difficult for most
situations. An update query would easily change the ZLS entries to Nulls:
1. Create a query, and under the problem field enter the criteria:
""
2. Change it to an Update query (Update on Query menu).
3. In the new Update row, enter:
Null

As to how they got there, it could be:
- Default value set to "".
- Programmatically clearing the field by assigning "" to the text box.

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

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

My "Track name AutoCorrect info" box was checked, so it may have been the
culprit. I have cleared it. You make another point that raises new
questions for me, however (below).

BTW, having AZL on makes no sense for most fields. Users are guaranteed
to
get confused between a Null and a Zero Length String (ZLS),not to mention
developers. Access itself can't tell the difference properly: DLookup()
wrongly reports Null for a ZLS.

* I wouldn't think I would need to have this on, except that after the AZL
field properties changed, my application was throwing an error message
when
it encountered a field that (I thought) contained Null. Based on this
behavior, I figured that Access did not distinguish between the two. This
concept is new to me; I always figured that if the user entered nothing,
or
deleted the contents of a text field, then the field would be Null, not a
ZLS.

I have run the following expression in a query to analyze the contents of
one of the affected fields:

IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;""ZLS"""))

I am surprised to see that in my table of 241 records, there are 157 Nulls
and 47 ZLSs, the other 37 contain text entries. It is easy enough to
change
a ZLS to a null: I can either put a space character in the empty field
(Access must interpret this as an entry of 'nothing' into the field and
convert it to a null value), or I can enter text and delete it. I see
that
if I try to enter a pair of quotes (""), I get the error message "Field
can't allow zero-length string." What I can't figure out is how those
fields became ZLSs in the first place. I don't imagine there were any
users
entering double quotes in the field when they wanted it blank!

If I can't figure out how my table became populated with ZLSs, I will need
to use the AZL property in the table to avoid error messages. I could
always use error-trapping, and "Resume Next" on that specific error, but I
get nervous when I push past Access' warnings that way.

Any other insights?

TIA
Paul

 

[Paul Johnson]

Yes, the update query will fix the immediate problem, and since my
application programmatically imports records to the table in DAO, I will
also add code to the import module to look for ZLSs in the fields and
substitute Nulls in their place. Beats "Resume Next" statements.

Thanks for the insight.

Paul

Hi Paul.

Yes, the ZLS is way too confusing and unnecessarily difficult for most
situations. An update query would easily change the ZLS entries to Nulls:
1. Create a query, and under the problem field enter the criteria:
""
2. Change it to an Update query (Update on Query menu).
3. In the new Update row, enter:
Null

As to how they got there, it could be:
- Default value set to "".
- Programmatically clearing the field by assigning "" to the text box.

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

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

My "Track name AutoCorrect info" box was checked, so it may have been the
culprit. I have cleared it. You make another point that raises new
questions for me, however (below).

BTW, having AZL on makes no sense for most fields. Users are guaranteed
to
get confused between a Null and a Zero Length String (ZLS),not to mention
developers. Access itself can't tell the difference properly: DLookup()
wrongly reports Null for a ZLS.

* I wouldn't think I would need to have this on, except that after the AZL
field properties changed, my application was throwing an error message
when
it encountered a field that (I thought) contained Null. Based on this
behavior, I figured that Access did not distinguish between the two. This
concept is new to me; I always figured that if the user entered nothing,
or
deleted the contents of a text field, then the field would be Null, not a
ZLS.

I have run the following expression in a query to analyze the contents of
one of the affected fields:

IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;""ZLS"""))

I am surprised to see that in my table of 241 records, there are 157 Nulls
and 47 ZLSs, the other 37 contain text entries. It is easy enough to
change
a ZLS to a null: I can either put a space character in the empty field
(Access must interpret this as an entry of 'nothing' into the field and
convert it to a null value), or I can enter text and delete it. I see
that
if I try to enter a pair of quotes (""), I get the error message "Field
can't allow zero-length string." What I can't figure out is how those
fields became ZLSs in the first place. I don't imagine there were any
users
entering double quotes in the field when they wanted it blank!

If I can't figure out how my table became populated with ZLSs, I will need
to use the AZL property in the table to avoid error messages. I could
always use error-trapping, and "Resume Next" on that specific error, but I
get nervous when I push past Access' warnings that way.

Any other insights?

TIA
Paul