WHERE isnull in vba

[icemonster]

so am trying to figure out the proper way to write this:

strWheresSql1 = "WHERE qry1.field1 = Is null "

seems not to be working?

 

[Isskint]

I am not sure when you would need to use such a criteria.

If a field has nothing in it, it would surely be picked up with

• "WHERE qry1.field1 = """"".

Does a checkbox's 3rd state qualify as null? if so i would have thought

• "WHERE ((qry1.field1 <> TRUE) AND (qry1.field1 <> FALSE)) would do that.

OR (just thought of this)

• "WHERE IsNull(Field1)"

 

[VilaRestal]

just remove the equals:

strWheresSql1 = "WHERE qry1.field1 Is null "

 

[Pat Hartman]

Isskint,
"" which is a zero-length-string (ZLS) is NOT the same as a null. So, "WHERE qry1.field1 = """"" will not find null values.

 

[Isskint]

Thanks Pat, I sort of 'overlooked' that one.... (memo to self think b4 posting)

 

[Galaxiom]

It is important to realise that the comparator keyword for Null is "Is".

I expect Is is used because Null is actually more like an object (an empty one) than a variable and is thus treated more like and object than a variable.
In VBA we write:

If SomeObject Is Nothing Then

Rather Than:

If SomeObject = Nothing Then

Indeed Null doesn't actually equal anything.
The following expressions both return Null (which will be interpreted as False).
(Null = Null)
(Null <> Null)

So we use:
IsNull(Null) to get a True output.

BTW This expression returns False so Null and Nothing are not the same.
IsNull(Nothing)