I am trying to run a query based on an "Option Group" on my 'frmReports' form

If '1' is chosen the query should return records containing 1, 2, 3 or 4; if option 2 is chosen it should return records for '5' only; if option 3 is chosen it should return records based on a combo box in which 1 thru 5 are available for selection.

The main goal is be be able to see 1 - 4 as one group or 5 as another group while having the ability to select any of the 1 -4 items seperately. This is a weird setup but it is what the boss wants to see.

Here is the code in my query:

IIf([Forms]![frmReports]![ChooseLocn]=1,1 Or 2 Or 3 Or 4,IIf([Forms]![frmReports]![ChooseLocn]=2,5,IIf([Forms]![frmReports]![ChooseLocn
]=3,Like "*" & [Forms]![frmReports]![ComboChamber])))


Any suggestions?

Just wondering why the "1 or 2 or 3 or 4" option won't work. I can specify any one of these values and the query runs. Also, it won't run the option 3 code using the ComboChamber combo-box.

I think you will be better off developing separate queries to do each set of conditions. You could then open the report via code in Design View, Hidden Window, and change its Record Source to the appropriate query. Or even develop separate reports for each set of conditions.

Frank may have the best solution, but if you want to try to keep your current setup, maybe you can simplify the query by pointing the criteria to an unbound textbox control on your form that will contain the necessary criteria depending on the choice in the option box.

In your form, create a hidden textbox (let's call it txtChoice), then in the AfterUpdate of the option group, you can put:

Select Case ChooseLocn
Case 1
Me.txtChoice = "Val(1) OR Val(2) OR Val(3) OR Val(4)"
Case 2
Me.txtChoice = 5
Case 3
Me.txtChoice = Me.ComboChamber
End Case

Note: The above code is untested. I wasn't sure how to pass numbers to the query - maybe Val() will work.

Then in the query, you can specify the criteria to simply say:

[Forms]![frmReports].[txtChoice]

HTH.