Choose 2 of 3 options in query.

[stephen81]

I have a table with 2 fields (for simplicity). These are called ID and Accuracy. Accuracy can have either "Correct", "Major Error" or "Minor Error" as a value.
I want to create a report with Accuracy as the criteria which I am doing with a combo box on a form so the user can select one of the three values (or leave it blank to select them all).
The problem I have is that I would also like to be able to report on "Major Error" and "Minor Error" on the same report. I have added a fourth option to my combo box, "Major/Minor Error" and I was planning to use an iif statement as the Accuracy criteria in my query so that if "Major/Minor Error" is selected the criteria becomes "Major Error" or "Minor Error". The query does work if I select one of the first three options but if I choose "Major/Minor Error" I get an error saying
'This expression is typed incorrectly, or is too complicated to be evaluated'.

Can anyone tell me how to get around this?

 

[EMP]

I was planning to use an iif statement as the Accuracy criteria in my query so that if "Major/Minor Error" is selected the criteria becomes "Major Error" or "Minor Error".

Try this setting in a column in the query grid (replacing with the correct form name and combo box name):-

Field: IIf([Forms]![FormName]![ComboBoxName] Is Null,True,IIf([Forms]![FormName]![ComboBoxName]="Major/Minor Error",[Accuracy] In ("Major Error","Minor Error"),[Accuracy]=[Forms]![FormName]![ComboBoxName]))

Show: uncheck
Criteria: <>False

 

[stephen81]

Excellent. That works perfectly.
I did find a solution similar to this under another post but I couldn't get that one to work.

Thanks EMP

 

[GGib7711]

Very interesting solution, but I just don't understand.


Why should the criteria be placed in the Field row instead of the Criteria row?

What do the True in the IIF expression and the <>False in the Criteria row do?

 

[EMP]

The <>False in the Criteria row tells Access to treat the IIF expression in the Field row as the criteria.

The word True in IIf([Forms]![FormName]![ComboBoxName] Is Null, True, ... will return every record if the combo box is left blank.


If we put an IIF expression in the Criteria row, Access will add an invisible = sign in front of IIF.  So unless all the true- and false-parts returned by the IIF can properly use this invisible = sign as their respective operators, we just can't put an IIF criteria in the Criteria row.

In Stephen's expression, True does not require an operator, while "Major/Minor Error" needs the IN operator, whereas the other selections need the = operator.  Since not all of them use an = operator, the IIF criteria has to be put in the Field row instead (with <>False in the Criteria row).

When putting an IIF in the Field row, the important thing is to properly incorporate the field name in the expression.

Hope this helps.

 

[GGib7711]

Thank you for the clear explanation. Now I know when an IIF criteria should be placed in the Field row with <>False in the Criteria row.

Thanks again.

 

[EMP]

Now I know when an IIF criteria should be placed in the Field row with <>False in the Criteria row.

When I need to use an IIF expression in the criteria, this is what I usually do:-

After linking the tables/queries and selecting the fields in query Design, I just switch to SQL View and type the IIF expression as a Where clause in the SQL statement. This way, I don't need to worry about which row to put the IIF expression in and I don't even need to type <>False.