Query Filter Help

  • Thread starter Thread starter Jason Rhymes
  • Start date Start date
J

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
 
This is a good job for the Choose function rather than nested IIfs.
Look it up in the help file.

-Tom.
Microsoft Access MVP
 
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
Click to expand...

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.
 
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")
 
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
 
Back
Top