If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Allow Zero Length
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? |
#2
|
|||
|
|||
Allow Zero Length
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 he 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" wrote in message news 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? |
#3
|
|||
|
|||
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" wrote in message news 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? |
#4
|
|||
|
|||
Allow Zero Length
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 he 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" wrote in message news 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? |
#5
|
|||
|
|||
Allow Zero Length
On Wed, 12 Aug 2009 05:40:02 -0700, Doctor
wrote: Because it is too easy to confuse a ZLS with a Null value. -Tom. Microsoft Access MVP 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? |
#6
|
|||
|
|||
Allow Zero Length
"Allen Browne" wrote in
: 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/ |
#7
|
|||
|
|||
Allow Zero Length
"David W. Fenton" wrote in message
36.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. |
Thread Tools | |
Display Modes | |
|
|