Why do Access comparison operators return "null"
Mike Sacca
I am trying to figure out why Access comparison operators can return a value
of "null" in addition to the values of "true", and "false".
I have many occasions where I need to perform a not equal comparison between
text fields and I have recently found that if EITHER of the comparison fields
is null, then null is returned. For example, I need to compare an
Organization's mailing address against the mailing address of people who work
at the organization (in some instances the people want their mail at a
different address). If any address line is different then I set a
SameAddressIndicator field to "No".
The problems occurs when any address line contains missing data. For example,
if I compare the organization's address2 line against the person's address2
line and one has data and one does not then NULL is returned, instead of
FALSE.
I have had to write several extra conditions to account for this which seems
inefficient.
Anyone experience the same type of problem or have a simple solution?
If replying via E-Mail, please reply to nj1pro...@sdps.org
Many Thanx!
Mike Sacca
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Allen Browne
Is 5 less than Some-Unknown-Number?
Answer: You don't know!
That means the expression
If [MyField] > 5 Then
does not calculate to True or False if [MyField] is Null.
There's just no way to know whether it's greater or less,
so the result of the comparison is "unknown" (i.e. Null).
Likewise a block of code starting with:
If [MyField] = Null Then
will *never* execute. Even if the field happens to be Null,
the line of code is equivalent to:
Is one unknown thing the same as another unknown thing?
The result will always be "Dunno", i.e. the outcome is Null.
Instead, the programmer should have used:
If IsNull([MyField]) Then
Nulls are incredibly important in relational databases. The
fact that you *don't* know a value (such as someones date of
birth) is actually important. In fact, it's a crucial piece
of information if you are trying to calcualte the average age
of people in your databases. Nulls just don't get included in
the calculation of an average. Can you see how they would slew
the result if they did?
It's not difficult to learn to program with Nulls in mind.
For those occasions where you want to make an assumption for
an unknown value, use the Nz() function.
Examples:
1) To treat a Null as a zero in a number field:
If Nz([MyNumberField],0) = 0 Then
2) To treat a Null or zero-length string the same:
If Len(Nz([MyTextField],"") = 0 Then
3) To treat a Null date field as the system date:
If Nz([MyDateField], Date()) < #1/1/2000#) Then
4) To treat an unbound check box as unchecked:
If Nz([MyUnboundCheckbox].Value, False) Then
5) For the example you gave:
If Nz([Address1],"") = Nz([Address2], "") Then
MsgBox "Both the same."
End If
You will eventually come to think of the Null as a friend.