Allow Zero Length

[Doctor]

Why shouldn't I Allow Zero Length on Text Fields? I've read somewhere that
this is not a good practice.

Are there any circumstances where I should leave a field set to Allow Zero
Length.

Also if I changed this in my database, how would it affect queries and
search forms that are already built?

 

[Allen Browne]

If a field contains no data, it is Null. If the field contains a string with
no characters in it, it is a zero-length string (ZLS.)

A zero length string is not the same thing as a Null: there are quite
significant differences in the way they work. The way nulls work is outlined
here:
http://allenbrowne.com/casu-12.html

In general, you should decide whether a field can be left blank (nullable)
as most field should, or if the field must contain data (required.) If you
then want to allow a zero-length string as a valid data (remember that IS an
entry, it's not the same as null), they you should set Allow Zero Length to
Yes.

In theory, you might use a ZLS to indicate that the value for a field is
known to be non-existent, as distinct from being merely unknown. So if you
know that someone has no phone number, you could represent that as a ZLS,
whereas a Null would mean that we don't know if someone has a phone number.

In practice, there's no visible difference to the end user between a ZLS and
a Null, so it is is rarely useful to allow zero-length strings in your
database. All you are going to do is to confuse the heck out of an end user,
who can't see any visible difference between a ZLS and a Null, and won't
understand the data.

There are rare cases where a ZLS may be useful. For example, say your
database is managing the hiring of space in commercial offices. You have a
table of properties which contains fields like this:
- Office number
- Street number
- Street name
- Suburb
- Zip
You want to ensure that the combination of those fields is unique (so you
can't enter a property twice), so you place a unique index on the
combination. Then you realize that the Office Number is not relevant for
some properties that can't be subdivided. You could treat them as being just
office number 1, but that doesn't really look good on the address panel. So,
you decide to use a ZLS for that field. You can enforce uniqueness on the
ZLS, so you can't enter that same address as a duplicate record.

That's about the only kind of example I can think of, where a ZLS would be
useful. In general, allowing a ZLS is a poor design: you're not setting up
for the best data, and you will confuse the user.

Perhaps the article you read was this one:
Problem properties
at:
http://allenbrowne.com/bug-09.html

 

[Gina Whipp]

Doctor,

In what context? I have fields that I allow zero length but quite a few
that I don't, it depends on what I am using the field for. Are we talking
about Notation fields, Address fields or fields that will be used in
calculations but then there are options to fix those who insist on removing
the '0'.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Doctor]

Thank you. That was very clear and helpful.

If a field contains no data, it is Null. If the field contains a string with
no characters in it, it is a zero-length string (ZLS.)

A zero length string is not the same thing as a Null: there are quite
significant differences in the way they work. The way nulls work is outlined
here:
http://allenbrowne.com/casu-12.html

In general, you should decide whether a field can be left blank (nullable)
as most field should, or if the field must contain data (required.) If you
then want to allow a zero-length string as a valid data (remember that IS an
entry, it's not the same as null), they you should set Allow Zero Length to
Yes.

In theory, you might use a ZLS to indicate that the value for a field is
known to be non-existent, as distinct from being merely unknown. So if you
know that someone has no phone number, you could represent that as a ZLS,
whereas a Null would mean that we don't know if someone has a phone number.

In practice, there's no visible difference to the end user between a ZLS and
a Null, so it is is rarely useful to allow zero-length strings in your
database. All you are going to do is to confuse the heck out of an end user,
who can't see any visible difference between a ZLS and a Null, and won't
understand the data.

There are rare cases where a ZLS may be useful. For example, say your
database is managing the hiring of space in commercial offices. You have a
table of properties which contains fields like this:
- Office number
- Street number
- Street name
- Suburb
- Zip
You want to ensure that the combination of those fields is unique (so you
can't enter a property twice), so you place a unique index on the
combination. Then you realize that the Office Number is not relevant for
some properties that can't be subdivided. You could treat them as being just
office number 1, but that doesn't really look good on the address panel. So,
you decide to use a ZLS for that field. You can enforce uniqueness on the
ZLS, so you can't enter that same address as a duplicate record.

That's about the only kind of example I can think of, where a ZLS would be
useful. In general, allowing a ZLS is a poor design: you're not setting up
for the best data, and you will confuse the user.

Perhaps the article you read was this one:
Problem properties
at:
http://allenbrowne.com/bug-09.html

 

[Tom van Stiphout]

On Wed, 12 Aug 2009 05:40:02 -0700, Doctor

Because it is too easy to confuse a ZLS with a Null value.

-Tom.
Microsoft Access MVP

 

[David W. Fenton]

In general, you should decide whether a field can be left blank
(nullable) as most field should, or if the field must contain data
(required.) If you then want to allow a zero-length string as a
valid data (remember that IS an entry, it's not the same as null),
they you should set Allow Zero Length to Yes.

I find it quite annoying that somewhere along the line, Microsoft
changed Access to default to ZLS=Yes in the table designer. It
wasn't that way in A2000, but it is in A2003 (I never used A2002
enough to know which it used).

Is there some way to change the default text field properties that
the table designer will use?

 

[Keith Wilby]

Is there some way to change the default text field properties that
the table designer will use?

Not that I know of but there's code on Allen's site that will change the
property in all of the tables in a db file.

Keith.