Table Field Default to ZLS

Is it a bad idea to set all fields, where possible, in a table to default to a ZLS by placing "" in the field's default value when creating a table?  I have been working with databases since dBaseII in the '80s and my applications are for in-house use, never to be sold, and not huge file sizes, over a local domain, not clout based, no SQL server.  And, I don't want to create complicated VBA to create an SQL that will ignore Nulls, I get by with some VBA but I'm no expert.  When I filter by form (sometimes multiple field filters) and I place text in the "filter" text box & requery/refresh in an "OnExit" event, I do not want records with null values for that field to show in the dataset.

My last application included filter-by-form fields that proved to be very difficult due to Null values in the table fields.  I was pulling my hair out trying to not show null values for a particular filter field.  I don't use the "Form Filter" in the form itself, I find this to be troubling and unstable.  I use criteria in the underlying query matched to filter fields in the form header.

What I also find troubling is that fact that when I have a record field with text and I delete all text within the field the value is a ZLS, not Null.

Can anyone give me a valid reason why I shouldn't completely eliminate Null values and force ZLS by default in my table(s) based on above?

Help is appreciated.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Is it a bad idea to set all fields, where possible, in a table to default to a ZLS by placing "" in the field's default value when creating a table?  >>

 It's not "bad" and it's not "good" either; it depends on what you want to achieve.

Some dislike having nulls anywhere, so they set all fields.  That's a valid approach and there's no real downsides other than making a mistake and not filling a field, at which point your app doesn't handle it well because you didn't account for nulls.

But Nulls do indicate something different than a ZLS.  A "null" means value unknown.   A ZLS is a value.

For example, a middle name.   Null means we don't know if there is one or not, but a ZLS means we do know that the person does not have one.

cwbarrettAuthor Commented:
Thanks Jim, good info.  A week ago I did some testing and came to the conclusion that entering text into a field, saving the record, then returning to the same field/record and deleting the text would result in a ZLS, not null.  But I tried it again today with different results, manually deleting text results in a null.   So now I have to reevaluate the whole ZLS thing.

Seems to me that Access used to have the option "Allow Null Values" in table setup, but I don't see it now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I never allow ZLS. Here's the first problem. Visually, you cannot tell the difference between a Null and a ZLS.  So, when creating a query  I makes creating criteria on a Text field that 'may' contain a ZLS or a Null.  I choose to never worry about this,  and thus never all ZLS. Unfortunately, I have to deal with ZLS frequently because we get a lot of 'reports' (XLS or CSV files) wherein Text fields contain ZLS.  So, during importing, I check for ZLS ("") and convert to Null if necessary.

It PAINS me to no end that Microsoft chose to have the Default for Text data types as Allow ZLS !


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

cwbarrettAuthor Commented:
I'll plan on building an SQL to redefine the WHERE after I change one of the four "filter-by-form" fields in my form.  I'll use the iff() function to see if the form's search criteria is null.  SQL WHERE criteria will change from   Like "*" & [Forms]![frmLeadsList]![txtAddress1Filter] & "*"   to   Like "*" & [Forms]![frmLeadsList]![txtAddress1Filter] & "*" or is null  depending on whether the filter text box has text or not.

I'm not great at VBA so it'll take time for me to figure out.  What's interesting is if I open the form and do a filter by form it shows nulls.  But if I open the query directly with the same criteria and of course the form is open, the direct query run doesn't show the nulls.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I think you should have split the points with Jim ...  who gave equally valid input.
cwbarrettAuthor Commented:
Thanks for pointing that out Joe.  Sorry Jim, sometimes I click w/o thinking.  Can I change?  If there is a way to change I will.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
No worries...never been in it for the points and Joe's comment points out to future readers  that they  should consider the points I made .

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.