Prev: Returning Only Blank Values
Next: Nedd a little guidance on a data capture scenario from Oracle
From: David W. Fenton on 13 Mar 2010 14:39
"Albert D. Kallal" <PleaseNOOOsPAMmkallal(a)msn.com> wrote in
news:%SKmn.13884$QL4.716(a)newsfe24.iad:

> In other words, you're not solving or avoiding having to deal with
> null columns. Since that's the case, most of us find it better to
> adopt a programming standard that nothing in a column is saved as
> null. And it means all over code by adopting this standard it
> allows us to work with those nulls that you can't avoid anyway.

Excellent thoughts, Albert. But I think a more clear way to say is
that with ZLS off, Null means only one thing, that the value in the
field is unknown. It's possible to impute meaning to a ZLS string as
distinct from Null, but as you say, it's hard to maintain that
distinction in a field (though I guess Not Null as validation rule
and "" as default value would prohibit a stored value of Null), let
alone with outer joins.

I have never understood the irrational fear of Nulls, to be honest.
That said, most of the times I've encountered ZLS's has been in
databases built by people who were just beyond their level of
comprehension, and in most cases did it because they had to append
data with ZLS's in the fields (this has very often been the case
when a web application was involved), and didn't know how to work
around it. That is, allowing the ZLS had no actual particular
meaning, it was just a kludge to work around a problem created by a
problem beyond the programmer's ability to solve.

Me, I wrote a ZLS-to-Null function years and years ago. It's
appended after my signature (from a period in which I appended
Leszinski-Reddick prefixes to my function names to indicate return
type -- I don't do that systematically any longer, only when there's
possible ambiguity).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function varZLStoNull(varInput As Variant) As Variant
If Len(varInput) = 0 Then
varZLStoNull = Null
Else
varZLStoNull = varInput
End If
End Function
From: hbinc on 13 Mar 2010 16:13
On Mar 13, 8:39 pm, "David W. Fenton" <XXXuse...(a)dfenton.com.invalid>
wrote:
> "Albert D. Kallal" <PleaseNOOOsPAMmkal...(a)msn.com> wrote innews:%SKmn.13884$QL4.716(a)newsfe24.iad:
>
> > In other words, you're not solving or avoiding having to deal with
> > null columns. Since that's the case, most of us find it better to
> > adopt a programming standard that nothing in a column is saved as
> > null.  And it means all over code by adopting this standard it
> > allows us to work with those nulls that you can't avoid anyway.
>
> Excellent thoughts, Albert. But I think a more clear way to say is
> that with ZLS off, Null means only one thing, that the value in the
> field is unknown. It's possible to impute meaning to a ZLS string as
> distinct from Null, but as you say, it's hard to maintain that
> distinction in a field (though I guess Not Null as validation rule
> and "" as default value would prohibit a stored value of Null), let
> alone with outer joins.
>
> I have never understood the irrational fear of Nulls, to be honest.
> That said, most of the times I've encountered ZLS's has been in
> databases built by people who were just beyond their level of
> comprehension, and in most cases did it because they had to append
> data with ZLS's in the fields (this has very often been the case
> when a web application was involved), and didn't know how to work
> around it. That is, allowing the ZLS had no actual particular
> meaning, it was just a kludge to work around a problem created by a
> problem beyond the programmer's ability to solve.
>
> Me, I wrote a ZLS-to-Null function years and years ago. It's
> appended after my signature (from a period in which I appended
> Leszinski-Reddick prefixes to my function names to indicate return
> type -- I don't do that systematically any longer, only when there's
> possible ambiguity).
>
> --
> David W. Fenton                  http://www.dfenton.com/
> usenet at dfenton dot com    http://www.dfenton.com/DFA/
>
> Public Function varZLStoNull(varInput As Variant) As Variant
>   If Len(varInput) = 0 Then
>      varZLStoNull = Null
>   Else
>      varZLStoNull = varInput
>   End If
> End Function

Hi Albert and David,

You are completely right about the use of NULL-values, because that is
the way Access works!
And that is good, for Dates and Numerics.

With Text- and Memo-fields it is a little different. There is
information in that field (Len > 0) or not.
In the latter case you can describe that with NULL (Standard Access)
or ZLS.
The end-user does not see any difference between NULL of ZLS.

I am a little pragmatic. I have not met a situation where I had to use
NULL instead of ZLS, but I have a couple of examples it is profitable
to use ZLS instead of NULL. Though using Standard Access is also very
advantageous.
LEFT JOIN's do return NULL values, but you can easily catch them with
testing for Value > "". To find out which records have no LEFT JOIN I
usually test for ForeignKey = NULL.
And indeed, it is very handy to use the string-operator "+", that
skips NULL-values, but a simple function can do the same on ZLS.
On the other hand, many Standard Access string functions can not
handle NULL values, and do return an error. So, it is a trade off.

I must admit that I am not a Standard Access user. I do not use
QueryDefs, Reports, Macros, Taskbars. I have generalized almost
anything, so that I use a couple of definition tables and a handful of
standard forms, in which all functionality (and more than Access
offers) is available, to make any application. Within this systematics
I feel more and more the advantages of ZLS, for string manipulation.


Here we are in a discussion group. My contribution is that there is
more than Standard Access.

HBInc.
From: David W. Fenton on 13 Mar 2010 22:25
hbinc <j.van.gils(a)hccnet.nl> wrote in
news:ecb9cd89-9274-44f4-9414-857be6061823(a)g28g2000yqh.googlegroups.co
m:

> On Mar 13, 8:39�pm, "David W. Fenton"
> <XXXuse...(a)dfenton.com.invalid> wrote:
> You are completely right about the use of NULL-values, because
> that is the way Access works!

Uh, it's the way the theory of relational databases work. Null is
essential to the theory.

> And that is good, for Dates and Numerics.

I don't really see why you think there's a necessary difference
between those data types and character-based fields. I see no
theoretical difference at all.

> With Text- and Memo-fields it is a little different.

....if you *choose* to define your storage rules that way, yes...

> There is
> information in that field (Len > 0) or not.
> In the latter case you can describe that with NULL (Standard
> Access) or ZLS.

Albert has pointed out why this is problematic. Did you read that?

> The end-user does not see any difference between NULL of ZLS.

But the database does. They are not the same, and you have to decide
if you're going to treat them as the same or not.

> I am a little pragmatic. I have not met a situation where I had to
> use NULL instead of ZLS, but I have a couple of examples it is
> profitable to use ZLS instead of NULL.

I have not encountered a situation where it was necessary to
maintain any distinction between ZLS and Null, which is the only
reason I'd ever allow the storage of a ZLS, i.e., because it meant
something distinct from a Null.

> Though using Standard Access is also very
> advantageous.
> LEFT JOIN's do return NULL values, but you can easily catch them
> with testing for Value > "".

If you want to eliminate the rows with Null values, sure, but if you
need them for other reasons (such as showing which main records lack
records in the other table), it will eliminate rows that you need.

> To find out which records have no LEFT JOIN I
> usually test for ForeignKey = NULL.

That's the usual approach -- nothing unusual about that at all.

> And indeed, it is very handy to use the string-operator "+", that
> skips NULL-values,

Huh? It doesn't skip them -- it propagates them. The & operator does
not. That is, "" & Null returns "", while "" + Null returns Null.

> but a simple function can do the same on ZLS.

But that is at the cost of executing the function, whereas without
ZLS's you can use VBA's built-in string handling.

> On the other hand, many Standard Access string functions can not
> handle NULL values, and do return an error. So, it is a trade off.

That is a *good* thing. You don't want to pass Nulls to functions
that can't handle them. In fact, many functions accept Nulls and
return Null -- it depends entirely on the function, and you have to
think about what you're passing to them.

Again, that is a GOOD thing.

> I must admit that I am not a Standard Access user. I do not use
> QueryDefs, Reports, Macros, Taskbars. I have generalized almost
> anything, so that I use a couple of definition tables and a
> handful of standard forms, in which all functionality (and more
> than Access offers) is available, to make any application. Within
> this systematics I feel more and more the advantages of ZLS, for
> string manipulation.

Sounds like an extremely narrow set of circumstances.

I have not argued against all usage of ZLS, if there is a GOOD
REASON to use them. I argue against it as default, that you should
start from the assumption that you don't want them, and use them
only when they have an advantage.

> Here we are in a discussion group. My contribution is that there
> is more than Standard Access.

You will find, I think, that you'll have a stronger rejection of
storing ZLS in any database forum than you're getting here. The
exception to that would be programming forums where the participants
are not really experts with databases, such as forums where web
development is discussed. There they love storing ZLS's since it
makes their lives easier since they don't really understand the
workings of relational databases.

But those are also the same people who prefer denormalized single
tables over proper normalization. So, lacking an understanding of
the advantages of proper relational database design, they do what
they can to get the job done. It's easier for *them* to use ZLS's
because they don't know the better way to do it.

That said, I again repeat that there can be scenarios where storing
a ZLS is useful.

But in my opinion, it's the exception rather than the rule.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Albert D. Kallal on 14 Mar 2010 00:19

"hbinc" <j.van.gils(a)hccnet.nl> wrote in message
news:ecb9cd89-9274-44f4-9414-857be6061823(a)g28g2000yqh.googlegroups.com...

>
> With Text- and Memo-fields it is a little different. There is
> information in that field (Len > 0) or not.
> In the latter case you can describe that with NULL (Standard Access)
> or ZLS.
> The end-user does not see any difference between NULL of ZLS.

> LEFT JOIN's do return NULL values, but you can easily catch them with
> testing for Value > "". To find out which records have no LEFT JOIN I
> usually test for ForeignKey = NULL.

Yes, you now telling me you going to test for Foreingkey = null? That is
exactly my point!. You now dealing with nulls! You now doing a test to check
for nulls. So, you telling me you going to test for null then?

And, to be clear, you can't go = null, you have to type in

is null

Your above assumes you going to be displaying the FK column. I don't have to
make that assumption. However, testing for invoice is null, or FK is null is
much the same thing. However, the user don't care, they can test either
column using the same way they been trained. You now suggesting to test FK =
null. What happens if they try testing for invoice number when they looking
at the query results? It going to be more intuitive for them to test invoice
is null when that what the user is asking for. (but, either way, it don't
matter since you suggesting to test for FK = null, and you not testing for
ZLS).

So, it just seems that when I want customers that don't have a invoice,
people will naturally will type in
= "" or is null (assuming they been trained that way). How are you ever
going to teach people when to test for a FK = null and all the other cases
when you need to use = "" and that approach will not work if they type in
that condition in the invoice column? However, in a query that is not a
join, when you want records without a invoice number, you now be testing for
ZLS strings.

So, that's when things break down. Sure, using > "" might work for
customers that have a invoice, but it not intuitive for those that don't.
And, most people will not know to type in > "", they will type in <> "", or
# "" (because that what will work for every other ZLS column in the system).
However, now here you are telling me your testing for a FK that is null. So,
you telling me you going to be dealing with nulls then? You not helping your
case here a lot.

I see little difference if testing FK is null, or invoice number is null. In
both cases you testing for nulls, and not ZLS, and that is my point.

And, what happens if you write any VBA code that process that sql in a
reocrdset? You again will have to deal with nulls in the code.

Since your code, your queries, and your conditions all have to deal with
nulls, it becomes difficult to avoid this.

I am just making the point that it becomes harder and harder to avoid nulls
over time as a application grows, and thus I making the case that you might
as well accept that you be dealing with nulls. So, then it seems to do it
the same way everywhere and never have to think or choose should you be
testing for ZLS, or some FK that is null.

If you not encountering this problem, then as mentioned, what works for you
just works fine. I not trying to change what works for you. I can't know
your particular situation.

However, even your above response shows that you suggesting to me to test
for a FK column that is null, and this shows you now having to deal with
nulls.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal(a)msn.com


First  |  Prev  | 
Pages: 1 2 3
Prev: Returning Only Blank Values
Next: Nedd a little guidance on a data capture scenario from Oracle