filtering null values with iif

[SunWuKung]

I am building a query which returns some records if I use Null as criteria in a field. If in the same field I use iif(5=5,Null,Null) it returns no record. I also tried Like .... "" but that didn't help either. Is it a problem with the iif function or (more likely) with the way I use it.
Thanks for the help.
SWK

 

[Alexandre]

The proper way to test for a null value is to use Is null( ). I believe the syntax is slightly different under SQL: IsNull.
However, I do not see at all what you are trying ot do with your iif:
iif(5=5, null, null)
It means if 5=5 then return Null, otherwise return Null
So obviously it will always evaluate to Null (nothing)

Beware also that "" is different from null so testing for a zero-lenght string "" will return different results than testing for a null value.

Alex

[This message has been edited by Alexandre (edited 03-13-2002).]

 

[SunWuKung]

Sorry if wasn't clear enough, but that was my point: the expression should surely evaluate to Null but if I enter this expression as a criteria in the query grid it doesn't filter out Null values, whereas if I simply enter Null as a criteria it does, so I think this must be an error in the way Access handles iif, or I don't know what is happening.
I am trying to filter out Null records if a certain criteria is tru - but it doesn't work and I am trying to find out why and I am trying to reduce the problem to its basics.

Thanks for the help.
SWK

 

[Alexandre]

False, are you sure? not Null?
In any case, false or null the explanation remains valid for SunWuKung.

Alex

 

[SunWuKung]

Hi Pat,
this is what I have entered for the criteria, what should have I used? Where do I put the IsNull?
Thks
SWK

IIf([Forms]![Narrative_AllocDetailMain]![OmittedScaleGroupFrom]="omitted",Null,[Forms]![Narrative_AllocDetailMain]![CompetencyID])

 

[Alexandre]

Pat, it is plain logic that the result of a conditional test should be either true or false. But these are the results of a few simple tests in the immediate window:

--Serie 1--
? 1=null
Null
? null=null
Null
? date() < null
Null

--Serie 2--
? null and -1
Null
? null not 0
Null
? null xor -1
Null

Even
? null and 4
Null

From which I had erronously deduced that logical comparisons involving null always evaluated to null. At least, that was consistent since it is like having a 'undetermined' or 'makes no sense' answer. Also happens with straightforward mathematical comparisons.

But then...
? null or True
True
? Null or 4
4

I d appreciate if you could enlight me on the logics behind the above.

Alex

[This message has been edited by Alexandre (edited 03-14-2002).]

 

[Rich]

I'm not sure I can explain but there is a distinct difference between a recordset returning Null values and one returning No values. Is Null will not return true if there are no values. IsNumeric or IsDate if used such as, If Not IsNumeric will return true on an empty recordset strangely, IsNull will not nor will it return false since neither condition exists. There is no record at all so it will return an error.
HTH

 

[SunWuKung]

Pat,
that was exactly what I was trying to do and I understood now how to do it.
Many thanks.
SWK