Default Table OPtions

  • Thread starter Thread starter Bruce
  • Start date Start date
B

Bruce

Win NT -> Office 2000 Pro

Whenever I create a new Access Table the fields default
to "Required: NO" "Allow Zero Length: NO:"

When I try to append data to the table I always get
the "Access could not add all your data ...XXx records due
to validation rules violation" error. I go change
the "Allow Zero Lenth" to "Yes", re-run ther append and
all's well.

How can I default the validation rule to "Yes" (or can I)
and how can Access say data is NOT required but it cannot
be a zero length string??
 
No. You cannot set a default for the table field property Allow Zero Length.

If you understand the difference between a Zero-Length String (ZLS) and a
Null, you probably do not want a ZLS in your field anyway. There is no
visible difference, but if you have the possibility of both, you must always
remember to use criteria of:
Is Null OR ""
everywhere you use this field, not to mention the need to explain the
difference to your users so they clearly understand when to use each and how
to filter for each.

A better solution might be to convert the ZLS to Null when appending your
data. In the Field row of your Append query, you would use:
IIf([MySourceField] = "", Null, [MySourceField])

Tables created in Access 2000 and later *do* default the AllowZeroLength
property to Yes, which is inconsistent with previous versions, and a real
pain for validation and data consistency. Our choice is therefore to run
code that turns off the AZL property for all text fields in the database
(with the possible exception of temporary tables used for importing garbage
and validating before writing to the real tables). The code to do that is
here:
http://allenbrowne.com/bug-09.html

If you are importing from other Access tables, you could run Update queries
on their text fields so that your source tables contain Nulls instead of
ZLSs, and then set their AZL property to No. That would be a permanent
solution to the problem.
 
Back
Top