Problem with Query criteria based on Form ComboBox

[skipjakk]

Have been able to create a query and link the criteria to a form combobox, but having problems trying to find a way to run the query if the user selects "ALL" in the combobox, so that the query will run all the combobox choices, have been trying this in the criteria:

IIf([Forms]![Frm Reporting Main]![cboOM]="ALL",Not "",[Forms]![Frm Reporting Main]![cboOM])

But this does not work, has anyone have any suggestions?

 

[PHV]

But this does not work
Some error message ? Not expected result ?
Perhaps a hidden column (the BoundColumn) in the combo with value = % for the ALL line displayed and the real value otherwise, and the the criteria is:
Like [Forms]![Frm Reporting Main]![cboOM]

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

 

[TonyJollans]

Hi skipjakk,

You don't show the context of your IIF but I suspect the [purple]Not ""[/purple] is the problem. Try doing ..

[blue][tt] ... WHERE ... YourField LIKE IIf([Forms]![Frm Reporting Main]![cboOM]="ALL","*",[Forms]![Frm Reporting Main]![cboOM]) ...[/tt][/blue]

You might also like to look at these FAQs on the subject ..

FAQ701-2330 (in the Access Queries and Jet SQL Forum), and
FAQ702-4538 (in the Access Forms Forum)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[skipjakk]

Thanks all, the "Like" was the answer:

Like IIf([Forms]![Frm Reporting Main]![cboOM]="ALL","*",[Forms]![Frm Reporting Main]![cboOM])

works great!


Anyone know of a way to REMOVE or ADD to a Criteria expression in a query? For example, if I wanted to add this or remove this from the query...