IIF Statement Troubles Yes, No, and Both (1 Viewer)

[monkeyman77]

I am having trouble with an IIF statement that i have in a query criteria:

IIf([Forms]![FormName]![Combo1]="OpenOnly",No,IIf([Forms]![FormName]![Combo1]="ClosedOnly",Yes,IIf([Forms]![FormName]![Combo1]="Open&Closed", {{{{This is where my issue is. I can't close the IIf statement. I need it to say Yes and No but the query won't run then}}}

Basically, i need my IIf statement to say
If combo box = OpenOnly Then No
If combo box = ClosedOnly Then Yes
If combo box = Open&Closed Then return all

 

[MarkK]

You need to check for Open AND Closed first.

 

[Ranman256]

You could make a translation table that has all cbo values,and what you want them to convert to. Join this table to your data in a query.

No code needed.

 

[smig]

What do you mean by "return all" ?

I think it will be much better to create a Function and use SelectCase in it.

 

[monkeyman77]

I guess I didn't provide enough info....

I have a table with a checkbox column to indicate open or closed records.

Then I have a form that will allow uses to pull the records they need; Open, Closed, or Both (this is what i meant by return all).

So I set up a query, added the checkbox column, and I am attempting to set the criteria to a IIF statement to allow for the filter to pull open, closed, or both (open and closed).

My issues is the both part. I can't seem to get the questions to work.

I created a sample database and if you try to run the form with Open&Closed selected you'll see nothing will happen. I don't know how to end the IIF statement in the query.


Thank you all for your suggestions! I am new to access but learning quickly!
-Steven

 

[JHB]

Run Query2.

 

[monkeyman77]

Yes!!! Thank you very much JHB!!!

 

[monkeyman77]

JHB, that works perfectly but now im curious as to why that works??? I thought -1 was equal to yes or true. Why does that return both yes and no???

 

[JHB]

JHB, that works perfectly but now im curious as to why that works??? I thought -1 was equal to yes or true. Why does that return both yes and no???

Let me see if I can give a good explanation.
I think it is easier to understand if I remove -1 (which only creates confusion), so Where statement is as below:

WHERE JobNumber=[Forms]![Form1]![Combo0] AND
OpenClosed=IIf([Forms]![Form1]![Combo2]="Open",No,IIf([Forms]![Form1]![Combo2]="Closed",Yes,[OpenClosed]));

In the first IF condition is all records returned that are equal to 0.
In the second IF condition is all records returned that are equal to -1.
If none of the two first conditions are met, it just look at the [Open Closed] and if it has a value, no matter whether it is -1 or 0, then return it.
I hope you understand my explanation.