IIF statement problem with null and not null

[clifftech]

Can someone help with this IIF statement.

I'm using this IIF statement in a query criteria grid. The form [Work by Client Form] has an option group used to determine whether to to select null and not null values. If the option group = 1 then both null and not null values are needed. If =2 then just not null values are needed.


IIf([Forms]![Work by Client Form]![ClientOptions]=1,Is Null Or Is Not Null, Is Null)

 

[Golom]

The query grid isn't smart enough to fill in the blanks

IIf([Forms]![Work by Client Form]![ClientOptions]=1,
1 = 1, 
[FieldName] Is Not Null)

 

[PHV]

You may try this criteria:
Is Not Null OR [Forms]![Work by Client Form]![ClientOptions]=1

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

 

[clifftech]

Golom

Your code results in no values being selected.

PH

Your code almost works. With [ClientOptions]=1 it results in all values however when it equals 2 it results in only not null values. I tried variations of your code but still doesn't come up with the correct results.

What I need is if [ClientOptions] =1 the result is both null and not null values, if [ClientOptions] =2 only null values. The field being filtered is a date field.

I appreciate your help.

 

[PHV]

Is Null OR [Forms]![Work by Client Form]![ClientOptions]=1

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

 

[clifftech]

PH,

That's it - works great. I'm not sure I understand the logic of the code and how it results in the necessary sql code - I'm used to PL/SQL. For this type of filter that uses a form option group, would you recommend a different method for coding the filter in the query?