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

 

[Pat Hartman]

It is not an error. It is simply one of the things you need to understand about nulls. The ONLY way to elicit a "true" result when dealing with a null field is to specifically test "IS NULL" in SQL or use the IsNull() function in VBA. If you compare two fields, both of which are null, the result will be "false" which is why with the IIf() criteria statement, Access returns no rows. You are effectively asking it to compare the values of two fields. When either or both are null, the comparison ALWAYS returns "false".

 

[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])

 

[Pat Hartman]

Alex, the result of a conditional test is either true or false. You're thinking of the destination field of a calculation. The result of SunWuKung's IIf() was in fact null however when that result is compared against the table field value, the result is either true or false. If the result is true, the record is selected, if false, the record is bypassed.

SunWuKung, I can't quite figure out what you are trying to do. So I'm going to make an assumption. If the value of a certain field is "omitted", you want to retreive records with null values. Otherwise you want to retrieve records that match a specific value. You need a compound condition to do this. The Where clause will look like:

WHERE (([Forms]![Narrative_AllocDetailMain]![OmittedScaleGroupFrom]="omitted" AND [CompetencyID] Is Null)) OR (([Forms]![Narrative_AllocDetailMain]![OmittedScaleGroupFrom] <> "omitted" AND [CompetencyID] = [Forms]![Narrative_AllocDetailMain]![CompetencyID]))

Hopefully all the parentheses and bangs are intact from my cut and paste. But basically the statement is (A = B and C Is Null) Or (A <> B and C = D). Since the statement contains AND, OR, and NOT the parentheses are VERY importand to the correct interpretation of the conditional statement.

 

[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

 

[Pat Hartman]

SWK, glad I guessed right.

Alex,
The following are conditional statements.
? IIf(1 = null,"true","false")
false
? IIf(null = null,"true","false")
false
I'm not sure what to call the statements you were typing in the immediate window.