Inconsistent treatment of Null

[Michael J. Strickland]

VB (and VBA) do not handle Null values consistently.

The statement "Null = Null" is a true statement.
VB incorrectly returns Null as the result.

The statement (Null & "") = (Null & "") is also a true statement.
VB correctly returns True as the result.

This inconsistency causes Primary Keys to fail as duplicate detectors.

With a one field Primary Key, two records each containing
Null in this key field are not considered duplicates
when in fact they are.

With a two field Primary Key, two records each containing
Null in one key field and identical values in the other
key field, are considered identical.




--

 

[Guest]

Allowing Nulls in a Primary Key is asking for troulbe; in fact, allowing
Nulls anywhere is bad. They latest Null Nightmare here was that I have to
import some Excel spreadsheets each month. My %#@#Y%^%$^ users will not take
the time to format numeric columns as some kind of number field in the
spreadsheets. Access imports them okay, but when you do any calculations in
a query and Null values are present, it does not create an error, it just
comes up with a wrong number. My users are bean counters and you know how
bean counters hate wrong numbers.

 

[Guest]

Hi,

Hopefully someone else with more expertise can expand on this, but I do not
agree that there is an issue.

"Null = Null" should return Null. You cannot compare the result of an
'unknown' values.

(Null & "") = (Null & "") performs the string concatenation of Null & ""
before trying to compare the two sides of the equlaity. Since the
concatenation returns an empty string, which is different than a Null string,
this will evaluate as True.

And for what it's worth, a key field can never be Null, so neither of the
statements about duplicate records can occur.

Jim

 

[Kevin K. Sullivan]

The statement "Null = Null" is a true statement.
VB incorrectly returns Null as the result.

Null does not equal Null. Null doesn't *not* equal Null. Nulls
propegate through any comparison operator or arithmetic operator even if
they are on both sides. Thus, if either argument is null, the following
operators will evaluate to Null:
=

<
=

<=
+
*
/
\
^
AND
NOT
EQV

There are special cases of two logical operators that do not propegate null:

(True OR Null) = True
(Null OR True) = True
(Null IMP True) = True
(False IMP Null) = True

The ampersand concatenation operator(&) does not propegate nulls: Null &
"" is a zero-length string. Therefore:

(Null & "") = (Null & "")
"" = ""
True

See Help topic "Comparison Operators" for the details.

Primary Keys cannot, by definition, allow Nulls in any of their
component fields. How, pray tell, are you getting data with said null
key fields into the table in the first place?

Kevin

 

[Michael J. Strickland]

Allowing Nulls in a Primary Key is asking for troulbe; in fact, allowing
Nulls anywhere is bad.

Agreed. However I am not generating these Nulls, MS is generating them when
it imports blank strings into table fields.

--

 

[Guest]

Your statements are correct;however, my post regarding importing from Excel
and the problems with Nulls is real.
You statement that Nulls cannot be allowed in a primary key are probably
(and should be) true. You can allow a zero length string, but that is not
the same as Null.

I am a little confused about your statment that Nulls are an 'unknown'
values. VB(A) may be confused by them, but logically they are a value.
Interestingly, I tried
IIf(Null, "Yes", "No") and IIf( Not Null, "Yes", "No") and both return No.
One wonders, then, how does the IsNull function work at all

 

[Michael J. Strickland]

Hi,

Hopefully someone else with more expertise can expand on this, but I do
not
agree that there is an issue.

"Null = Null" should return Null. You cannot compare the result of an
'unknown' values.

If Null = Null returns Null, so should Null & "" = Null & "".

(Null & "") = (Null & "") performs the string concatenation of Null & ""
before trying to compare the two sides of the equlaity.

And this concatenation should return a Null value not an empty string (to be
consistent).

And for what it's worth, a key field can never be Null, so neither of the
statements about duplicate records can occur.

Yes it can, hence the IgnoreNulls and Required properties.

Btw, I solved the problem by manually creating my own index field and
setting is as the
Primary Key. It can contain null values (which MS converts to blank strings
during the concatenation process).


--

 

[Paul Overway]

The problem isn't inconsistent handling...the problem is an
incorrect/invalid comparison.

To accurately compare two fields that may contain nulls, you need to use the
IsNull function or in SQL IS NULL/IS NOT NULL

(IsNull(Null) = IsNull(Null)) = True
(IsNull("Whatever")=IsNull(Null)) = False

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


VB (and VBA) do not handle Null values consistently.

The statement "Null = Null" is a true statement.
VB incorrectly returns Null as the result.

The statement (Null & "") = (Null & "") is also a true statement.
VB correctly returns True as the result.

This inconsistency causes Primary Keys to fail as duplicate detectors.

With a one field Primary Key, two records each containing
Null in this key field are not considered duplicates
when in fact they are.

With a two field Primary Key, two records each containing
Null in one key field and identical values in the other
key field, are considered identical.




--

 

[Dirk Goldgar]

If Null = Null returns Null, so should Null & "" = Null & "".

No. Concatenation is not the same as comparison.

And this concatenation should return a Null value not an empty string
(to be consistent).

Maybe it should, to be consistent, but it doesn't. Presumably someone,
somewhere, decided that the ability to convert Null to a zero-length
string by concatenation was too convenient to pass up.

Yes it can, hence the IgnoreNulls and Required properties.

An indexed field *may* contain Nulls, but a primary key field may not.

Btw, I solved the problem by manually creating my own index field and
setting is as the
Primary Key. It can contain null values (which MS converts to blank
strings during the concatenation process).

If you are converting Nulls to zero-length strings before storing them,
then your index is no longer storing Nulls.

 

[Michael J. Strickland]

Null does not equal Null. Null doesn't *not* equal Null

You can say the same thing about (Null & "").

Null should equal Null.

If Null means different things in different places, then it should be an
indicator of an indeterminate value only. It should not be a valid operand
for an expression (i.e. it should return an error when used as an operand in
any expression).

Nulls propegate through any comparison operator or arithmetic operator
even if they are on both sides. Thus, if either argument is null, the
following operators will evaluate to Null:
=

<=
+
*
/
\
^
AND
NOT
EQV

There are special cases of two logical operators that do not propegate
null:

(True OR Null) = True
(Null OR True) = True
(Null IMP True) = True
(False IMP Null) = True

The ampersand concatenation operator(&) does not propegate nulls: Null &
"" is a zero-length string. Therefore:

(Null & "") = (Null & "")
"" = ""
True

But to be consistent it should be Null not a blank string since an unknown
concatenated with a blank string could be anything.

It is not definitely a blank string, the way "True OR Null" is definitely
True.

--

 

[Dirk Goldgar]

I am a little confused about your statment that Nulls are an 'unknown'
values. VB(A) may be confused by them, but logically they are a value.

VB is not confused by Null, but it has special logic to handle it
because of its semantic meaning, which is "no value". Though we
sometimes speak of VB as being "confused" by Nulls, that's really just a
way of pushing our own misunderstanding off onto the compiler.

Interestingly, I tried
IIf(Null, "Yes", "No") and IIf( Not Null, "Yes", "No") and both
return No.

"IIf(Not Null, ...)" probably isn't doing what you think. Try this in
the Immediate window:

?null
Null
?not null
Null

What is the opposite truth value of "unknown"? "Unknown"!

One wonders, then, how does the IsNull function work at
all

I assume it checks a flag in the internal structure of the Variant data
type that says whether this variable is Null or not. (But you probably
weren't really looking for an answer.)

 

[Tim Ferguson]

The statement "Null = Null" is a true statement.
VB incorrectly returns Null as the result.

There are lots of problems with the correct use of Nulls in databases --
try googling or just turn to Date for erudite explanations.

Unfortunately, this is not one of those problems. It is a universal
understanding that Null is a non-value, not known, irrelevant, having no
informational value. Therefore it cannot be equal in any sense to another
Null -- the unknown value might be the same as something else, or it
might not be, you just cannot tell.

VB, VBA, Jet etc all make a reasonable bash at handling Nulls
appropriately, and returning a Null from most mathematical expressions
involving Null is a good system and pretty widely used.

You might try reading some more on R theory and database design.

Best wishes



Tim F

 

[Guest]

I considered the whole thread silly, so I responded in kind.

 

[Kevin K. Sullivan]

But to be consistent it should be Null not a blank string since an unknown
concatenated with a blank string could be anything.

It is not definitely a blank string, the way "True OR Null" is definitely
True.

There are two concatenation operators: & and +
+ works the way you describe above -- it lets Nulls propegate:
"Anything" + Null + "" = Null
Null + "" = Null
((Null + "") = (Null + "")) = Null

& has the special case of treating Null like a zero-length string when
one argument is null and the other is a string. Note that:

Null & Null evaluates to Null

Thus, you can convert any possibly-null variants to non-null string-type
variants with :
expression & ""

but

expression + "" preserves the null


It sounds like your primary key case involves an implicit conversion
from null into zero-length strings when you import data.

Kevin

 

[John Vinson]

Agreed. However I am not generating these Nulls, MS is generating them when
it imports blank strings into table fields.

If you set the table field's Allow Zero Length Strings property to
True, and its Required property also to True, it will import a blank
string as a zero length string value. This may be the solution here -
not have any Nulls at all.

John W. Vinson[MVP]

 

[Guest]

Okay, we have beat the crap out of null, lets get on to something else

Does Infinity = Infinity?
does it return Null or an Empty Universe?
does Null & Infinity = Empty Universe?
does Infinity / Empty Universe create an error
Is the an EI function that tests for an Empty Universe or Infinity?

Access Help is vague on this subject

 

[Dirk Goldgar]

Okay, we have beat the crap out of null, lets get on to something else

Does Infinity = Infinity?
does it return Null or an Empty Universe?
does Null & Infinity = Empty Universe?
does Infinity / Empty Universe create an error
Is the an EI function that tests for an Empty Universe or Infinity?

Access Help is vague on this subject

I think you're looking for the IsMeaningless() function.

 

[Guest]

YES!! THAT THE ONE
Dirk, ypu made my day.

I think you're looking for the IsMeaningless() function.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Oh, I tried it.
IsMeaningless(Null/Infinity,vbAbsurd)
It returns Infinity - 1

 

[Dirk Goldgar]

Oh, I tried it.
IsMeaningless(Null/Infinity,vbAbsurd)
It returns Infinity - 1

LOL