Access Criteria IIF Filter (Is Null)

[swaimdw]

Greetings, I am trying to filter a query using an Is Null within an IIf statement. Based on a response, I need to return a list of all records with a specific field either empty or not. The Is Null placed directly into the Criteria field along with the field name works fine (ie Is Null Or [Field_Name]) and either of these options work alone but...
When I use them inside an IIF statement it fails. Any ideas? Here is what my module looks like but I have tried numerous variations.
-IIf(response=2,Is Null Or [Field_Name],[Filter_Value])-
The Field name works alone but the Is Null does not either alone or as written.
Thanks
Darrell

 

[cmmrfrds]

Paste in your sql statement for the query.

 

[Blorf]

IIf(response=2,Is Null Or [Field_Name],[Filter_Value])-

It looks like your syntax is a little skewed.

Based on what I see, I can't be sure what you are wanting to do, but try this syntax

iif ([Response] is null, Do something, Else do something else)

Hope this is helpful,
ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[PHV]

Something like this ?
WHERE ...
AND ([Field_Name]=[Filter_Value] OR response<>2)
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swaimdw]

Thanks for responding.
cmmrfrds:
Are you asking me to paste in my entire SQL statement to this posting?
Blorf:
Response is a value from the dialog box before this query is to run. The response determines wether to filter on a specific value in the field or return all records. Without the Is Null only records with a value in the field are returned. If the field (which is not required in the table) has been left blank Access skips the record. Thus the query is missing some of the records needed.
PHV:
The IIf statement is already inside the Where statement in the query.

Perhaps I was unclear in my first posting. Continuing from above, If I use Is Null within the Iff statemnt, the query returns no records at all. If I use the Is Null by itself, the query returns all records where no value has been entered in the field in question. If I use the field name inside the Iff statment, the query returns all records where any value has been entered into the field in question. If I use the Is Null alone or with the field name inside the IFF, again the query returns no records at all.

Is the use of Is Null inside an IIf statement permitted?
Thanks again.
Darrell

 

[PHV]

Have you tried to replace your AND IIf stuff by this ?
AND ([Field_Name]=[Filter_Value] OR response<>2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RickSpr]

Is Null can't be used like that in an IIf expression.
Try this. Add a column to the filter (query) grid with the following settings:
Field: Expr1: Nz([Field_Name])
Show: (not checked)
Criteria: IIf(response=2, "" Or [Field_Name], [Filter_Value])

The Nz() function converts Null to a 0-length string, which is a value you can use in IIf().

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein