Using Null as query criteria

[jender624]

Hi all,

I'm trying to return all records in a query where the value of an integer is a null value. When I simply put "Is Null" in the criteria box, the query works. The problem is I need to include an IIF statement as criteria, like so:

IIf(GetValue("variable") <> 0, GetValue("variable"), Is Null)

GetValue is a function I use in VBA to return the value of a global variable, in this case "variable". The function returns a variant. So if the value of "variable" is 0, I want to return all records where the value of the field is NULL. However, when I try to employ this strategy, the query returns NO records. Like I said, if I just put "Is Null" in the criteria, it works and returns the records I expect.

What am I doing wrong?

 

[vbajock]

Try IIf(GetValue("variable") <> 0, GetValue("variable"), vbNullString)

 

[TonyJollans]

Hi jender624,

You cannot have operators (except in complete expressions) as output from IIfs, only operands. You must construct good SQL which does not depend on the results of a function for its construction, something like ..

[blue][tt]WHERE (GetValue("variable") <> 0 AND YourField = GetValue("variable"))
OR (GetValue("variable") = 0 AND IsNull(YourField))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.