Is Null or =Null

[Tim Chapman]

In a bit of code I have the following ".....blah=me.DEPTID......."

However id DEPTID is Null then the code fails because the string resolves as
"...blah=Null..." where as the correct syntax should be ".....blah Is
Null....."

How do I come around the problem of this syntax where one cannot use the
equals sign with Null?

 

[Scott McDaniel]

Use the builtin IsNull method:

If IsNull(Me.DEPTID) Then ...

or

If Not IsNull(Me.DEPTID) Then ...

 

[david epsom dot com dot au]

(blah=me.deptid) or ( isnull(blah) and isnull(me.deptid) )

or perhaps

nz(blah,0) = nz(me.deptid,0)


(david)

 

[Michel Walsh]

Hi,


Because NULL, in this context, mean UNKNOWN. If I ask you: Do my hair
color match the color of my car? What will you answer? Since you do not
know me, I may even be bald, my hair color, for all you know, is NULL
(unknown). Same for the color of my car, also NULL. So, my question is:

Null = Null ?

Eh, in all logic, you just can't tell. You do not know. So, the answer
if not TRUE, is not FALSE, it is NULL.

Null=Null returns Null, same as "red" = Null. You have to use

FieldName IS NULL
(in SQL) or

IsNull(FieldName)
( in VBA ) to ask Is the value unknown (Null).


Just remember that in database, there are THREE logical values, TRUE,
FALSE and NULL (UNKNOWN) and things will be fine.

As example, in VBA


If someTest Then
... part1
Else
... part2
End If


part1 is execute if the someTest if TRUE. ELSE, that includes false and
Null, part2 is executed. If you want to execute part1 if the test return
True or Null:


If Nz(someTest, True) Then
... part1
Else
... part2
End if


(Note that NOT NULL returns NULL )


Hoping it may help,
Vanderghast, Access MVP

 

[Kent Prokopy]

Not to sound like an ass or anything. But NULL means non existing. (No
record found that meets your criteria). NOT unknown.

Kent Prokopy

 

[david epsom dot com dot au]

Access only supports one type of Null. Several types of Null can
be identified and have been proposed. The SQL Null conflates several
different types of Null, including 'non-existing' and 'unknown'.
(0, "" and 'uninitialized' are another three sometimes included
as Null, but there are others as well).

(david)

 

[Michel Walsh]

Hi,



Null has probably hundred of meanings. It may mean Missing value, but also
Not applicable (hair color for a bald man), no matching data found as for an
outer join, and even "this is a group" (as in a result of a CUBE). If you
prefer, NULL is a single value overcharged with meanings, and one of them
is, indeed, Unknown, commonly seen as the Boolean meaning of Null.



Vanderghast, Access MVP

 

[Kent Prokopy]

My apologies, I was under the impression that NULL meant non-existing.