field validation help needed badly

[Karl]

re: MS Access 2000 & XP Pro

I must not understand something that seems as if it should be easy to
understand.

Given a text field, I set the "Required" rule to Yes. Yet, Access allows me
clear the field and post the record and close the table.

Why?

I've updated a column (with a query) to set all Null values to 'a'.

Then I try to change "Allow zero length" from Yes to No. When I try to save
the table, Access reports that "Errors were encountered".

If I set the field's Zero length property bact to Yes, then I can save the
table. So my point being is that nothing else is wrong with the data in the
table from Access's point of view.

I would really welcome some clarification on these two issues.

TIA

 

[Allen Browne]

From your symptoms, it sounds like you have some spaces or zero-length
strings in the field

Create an Update query to (Update on Query menu in query design) into this
table.

Under this field, in the Criteria row enter:
""
and in the Update row:
"z"
Run the query.

Change the Criteria row to:
Is Null
and run it again.

You have now converted all the nulls and zero-length strings to a Z.
Open the table in design view, and set the Allow Zero Length to No.
At the same time, make sure that the Default Value is not set for the field.
Particularly, it must not be a zero length string, i.e.:
""

 

[Karl]

Still no go. I'm unable to save the table when Zero Length is set to No.

When I ran the update query with criteria set to "", Access indicated that
one record was going to be changed. On subsequent runnings of this query,
Access says 0 rows will be changed. Ditto for "Is Null" criteria.

Any other ideas?

Thanks.


Oh, what's the difference between a field that is Null and a field that has
a value equal to ""?

This all is really a PIA.

 

[Allen Browne]

These things do work correctly, so you will need to take some time to
understand them. For a starting point, see:
Nulls: Do I need them?
at:
http://members.iinet.net.au/~allenbrowne/casu-11.html

The question is not specific to Access. In database theory and practice, a
Null represents the value Unknown or Not Applicable, whereas a zero-length
string represents a value that is known to be non-existent (such as a person
who is known to have no middle name, or no phone number).

 

[Karl]

Thanks for the pointer about Null.

Any idea as to why I can't set the "Allow Zero Length" string to Yes? (Upon
trying to save the table, it fails the validation test. I've also tried a
compact and repair.)

I ran an update query with "" as the criteria and Access says there are 0
rows to update.

 

[Allen Browne]

Well, there are possible reasons, such as corruption.
Or perhaps there are records that contain a Chr(0) which is considered to be
equal to a zero-length string in some contexts, but a 1-char string in
others.