Iff statement in query criteria

[BlueJacket]

Basically, I want the criteria to check to see if the Option Frame is null. If it is null, I want the criteria to be Is Null OR "1" OR "2" OR "3" OR "4". If one of the options is checked, I want it to read Like "*" & [forms]![frmMainSearch]![frameClosed] & "*".

Is this possible? I was thinking of something like:

IIf(IsNull([Forms]![frmMainSearch]![frameClosed]),Is Null OR "1" OR "2" OR "3" OR "4",Like "*" & [forms]![frmMainSearch]![frameClosed] & "*")

But I feel like quotation marks might be needed? And I'm still not sure how to make it return the if false statement with all of those other quotation marks.

Edit:

Or maybe this wouldn't work how I think it would...

My situation is this:

I have a main search form with a list box and I'm trying to incorporate some Option Frames to help filter out the list. The first option frame I want to use is to only show the properties in the list box that have either been Redeemed, Sold, or Reversed. There are other properties that haven't been Redeemed, Sold, or Reversed, and I want them to also show up in the list box when Option Frame is null.

 

[Ranman256]

your statement is invalid, and cant be done. not with wildcards.

 

[boerbende]

Think about building this up first with one statement, then with two etc.
I have not tested it, but I think to nest it will work
An example with one condition is
iif(condition1, Do If True, Do If False)
If this works, then replace the do if false with the new condition
iif(condition1, Do If True, iif(condition1, Do If True, Do If False))

etc.
Example with two conditions can become
IIf(IsNull([Forms]![frmMainSearch]![frameClosed]), Null,
IIf( [Forms]![frmMainSearch]![frameClosed] = ‘1’, “Like ‘*’”, “Nest here your next statement”))

(not really sure about the single quotes, maybe you need double "double quotes"

Hope this will give you a direction

Ben

 

[BlueJacket]

Actually, I did get it to work, though I'm sure there may be other/better ways.

I created a new query with a calculated field to put into my main query. I did this by using a nested IIF statement to give me the values that I needed.

ProgressID: IIf([tblRevenue]![RevenueTypeCode]=1,"1",IIf([tblRevenue]![RevenueTypeCode]=2,"2",IIf([tblRevenue]![RevenueTypeCode]=3,"3",IIf([tblRevenue]![RevenueTypeCode]=4,"4","0"))))