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
|
|||
|
|||
Best Text Field Defaults: Null or Zero Length String
Hi All,
I'm getting ready to start a new application. The first time I did a database app, I didn't put default value in for my tables' text (or memo) fields. The last time, except for a few cases where I needed some specific default, I set them all to zero-length strings (""), with zero-length strings allowed, of course. Now I'm getting ready to lay out my tables for this new app, and it occurs to me to ask the experts before I do this again. Are there issues I haven't noted that make doing this a bad idea? Is there a "best practice" for setting field defaults in tables? Thanks for the help. Regands, Max |
#2
|
|||
|
|||
Best Text Field Defaults: Null or Zero Length String
My preference is to allow Null fields where they make sense. I'd much rather
have Null for a person's middle initial if I don't know their middle initial than have a zero-length string. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Max Moor" wrote in message . 16... Hi All, I'm getting ready to start a new application. The first time I did a database app, I didn't put default value in for my tables' text (or memo) fields. The last time, except for a few cases where I needed some specific default, I set them all to zero-length strings (""), with zero-length strings allowed, of course. Now I'm getting ready to lay out my tables for this new app, and it occurs to me to ask the experts before I do this again. Are there issues I haven't noted that make doing this a bad idea? Is there a "best practice" for setting field defaults in tables? Thanks for the help. Regands, Max |
#3
|
|||
|
|||
Best Text Field Defaults: Null or Zero Length String
I have never allowed zero length strings. IMHO if there is no value to enter
into a field it should be null. -- Duane Hookom Microsoft Access MVP "Max Moor" wrote: Hi All, I'm getting ready to start a new application. The first time I did a database app, I didn't put default value in for my tables' text (or memo) fields. The last time, except for a few cases where I needed some specific default, I set them all to zero-length strings (""), with zero-length strings allowed, of course. Now I'm getting ready to lay out my tables for this new app, and it occurs to me to ask the experts before I do this again. Are there issues I haven't noted that make doing this a bad idea? Is there a "best practice" for setting field defaults in tables? Thanks for the help. Regands, Max |
#4
|
|||
|
|||
Best Text Field Defaults: Null or Zero Length String
Use nulls, never zero-length strings (ZLS).
In database theory, Null means things like: - unknown - not applicable - undefined - not yet assigned - to be announced ZLS means the value is known not to exist. For example, in a client table, someone might put a field called MiddleName. If you know that Jenny Jones has no middle name, then you could represent that data as a ZLS in the field, as distinct from Null which would mean we don't know if the person has a middle name. It would matter if you asked a question such as, "What percentage of our users have a MiddleName of 'Sam'?" Nulls are ignored in statistics (counting, averaging, etc), whereas ZLSs are not ignored. As an example, say you had 10 records: 2 x Null, 1 x ZLSs, 2 x 'Sam', and 5 other names. The percentage of Sams would be 2/8 = 25%, since the 2 nulls are not counted, but the ZLS is. In practice, that distinction is rarely of any practical use, and far too subtle for users to understand. Even developers get confused by the difference, so all it does is increase the chance that you get wrong answers (because the question has to be even more precise than the visible interface can see.) There is also a performance difference: testing for Null is faster than testing "" in JET. So, my recommendation would be to disable ZLS on all your text fields. I actually run this FixZLS() code on my databases after the schema is built: http://allenbrowne.com/bug-09.html There is a case where I do use ZLS, and that's where a field is required and part of a unique index. For example, if you are hiring out commercial addresses, you might create a unique index on UnitNumber + StreetNumber + StreetName + City (since you must not have 2 records for the same unit.) But sometimes UnitNumber doesn't apply. A ZLS in this field blocks duplicates, but allows it to be part of a unique index (even primary key.) Default Value is "". Some users are tempted to use ZLS so they don't have to learn to handle Null. Not a good idea: http://allenbrowne.com/casu-11.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. "Max Moor" wrote in message . 16... Hi All, I'm getting ready to start a new application. The first time I did a database app, I didn't put default value in for my tables' text (or memo) fields. The last time, except for a few cases where I needed some specific default, I set them all to zero-length strings (""), with zero-length strings allowed, of course. Now I'm getting ready to lay out my tables for this new app, and it occurs to me to ask the experts before I do this again. Are there issues I haven't noted that make doing this a bad idea? Is there a "best practice" for setting field defaults in tables? Thanks for the help. Regands, Max |
#5
|
|||
|
|||
Best Text Field Defaults: Null or Zero Length String
"Douglas J. Steele" wrote in
: My preference is to allow Null fields where they make sense. I'd much rather have Null for a person's middle initial if I don't know their middle initial than have a zero-length string. Thanks, Duane, Allen, and Doug. I couldn't have asked for a more credible concensus. Now I've got to go do some rethinking. :-) Regards, Max |
Thread Tools | |
Display Modes | |
|
|