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