Qry Based on Comboboxes 1

[jdgreen]

This is a little different from most other questions on having queries based on Combobox values. I have reports that are generated from a query. For each of these reports the users can select different options for the data to be displayed on the report. Using one field as an example, [Status], the combo box has four choices: Open, Closed, Void and All. I can get the query to work with the first three choices because they are all possible entries in the source table. The "All" is the problem. Since it is not a possible entry in the table I have tried numerous ways to use an IIF statement specifying if "All" is in the combobox then the criteria is to pull either Open or Closed records and otherwise to set the criteria equal to the value in the combobox. Here are a couple of ways I have tried:

WHERE ((tblShortages.Status)=(IIF(([Forms]![frmMain]![frmReports].[Form]![Combo50]="All"),(“Open" OR "Closed"),((tblShortages.Status)=([Forms]![frmMain]![frmReports].[Form]![Combo50]))))

and I also tried:

WHERE ((tblShortages.Status)=(IIF(([Forms]![frmMain]![frmReports].[Form]![Combo50]="All"),(((tblShortages.Status)="Open") OR ((tblShortages.Status)="Closed")),((tblShortages.Status)=([Forms]![frmMain]![frmReports].[Form]![Combo50]))))

John Green

 

[PHV]

WHERE (tblShortages.Status=[Forms]![frmMain]![frmReports].[Form]![Combo50] OR [Forms]![frmMain]![frmReports].[Form]![Combo50]="All")


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

 

[jdgreen]

That isn't going to do it. "All" is an option in the combobox, but it is not an entry option in the tblShortages.Status. I was trying to use the IIF statement because in some way I'm going to have to equate the "All" in the combobox to show the values it represents in the table.

John Green

 

[PHV]

That isn't going to do it
Have you tried my suggestion ?
What happen ?


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

 

[jdgreen]

My bad and my apologies. I don't really understand how it works, but it is. I'll need to study this one.

John Green