troubleshooting Question

Table Field Default to ZLS

Avatar of cwbarrett
cwbarrett asked on
Microsoft Access
7 Comments1 Solution120 ViewsLast Modified:
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.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Database Architect / Application Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2007

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros