Query Filter Help

[Jason Rhymes]

It's been a while since I wrote any code in access but I'm having to do a
little now. Having a hard time getting this to work and don't know what I'm
missing.
Basically I have a query field that looks at a form option box with 3
options to filter on. 1 and 2 works fine but 3 fails (option 3 is to show
both Order" And "Completed").

IIf([Forms]![JobList]![Status]=1,"Order",IIf([Forms]![JobList]![Status]=2,"Completed","Order"
And "Completed"))

Thanks
Jason

 

[Tom van Stiphout]

This is a good job for the Choose function rather than nested IIfs.
Look it up in the help file.

-Tom.
Microsoft Access MVP

 

[fredg]

It's been a while since I wrote any code in access but I'm having to do a
little now. Having a hard time getting this to work and don't know what I'm
missing.
Basically I have a query field that looks at a form option box with 3
options to filter on. 1 and 2 works fine but 3 fails (option 3 is to show
both Order" And "Completed").

IIf([Forms]![JobList]![Status]=1,"Order",IIf([Forms]![JobList]![Status]=2,"Completed","Order"
And "Completed"))

Thanks
Jason

Are "Order" and "Completed" the *only* values in the field and if 3 is
selected you wish to show all records?

Like IIf([Forms]![JobList]![Status]=1,"Order",
IIf([Forms]![JobList]![Status]=2,"Completed","*"))

The form must be open when the query is run.

 

[Jason Rhymes]

Thanks Tom for the quick response.
I just tried that and end up with the same problem.
Need option 3 to show both Order and Completed.

Choose([Forms]![JobList]![Status],"Order","Completed","Order" And
"Completed")

 

[Jason Rhymes]

Ahh, thanks Fred. This will probably work. I was lacking the Like statement
at the beginning.
I'll probably also use Tom's suggestion of using "Choose"

Like Choose([Forms]![JobList]![Status],"Order","Completed","*")

Thanks
Jason Rhymes