Allow Zero Length

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" <Doctor.TakeThisOut@discussions.microsoft.com> wrote in message
news:D9735F66-A299-4248-B469-0361C6F00B52@microsoft.com...
> 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?

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Doctor" <Doctor DeleteThis @discussions.microsoft.com> wrote in message
news:D9735F66-A299-4248-B469-0361C6F00B52@microsoft.com...
> 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?

Thank you. That was very clear and helpful.

"Allen Browne" wrote:

> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
>
> "Doctor" <Doctor.DeleteThis@discussions.microsoft.com> wrote in message
> news:D9735F66-A299-4248-B469-0361C6F00B52@microsoft.com...
> > 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" <AllenBrowne.RemoveThis@SeeSig.Invalid> wrote in
news:e2Ds8L1GKHA.4436@TK2MSFTNGP04.phx.gbl:

> 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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" <XXXusenet RemoveThis @dfenton.com.invalid> wrote in message
news:Xns9C65934DE7F72f99a49ed1d0c49c5bbb2@74.209.136.94...
>
> 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.