Use ComboBox as Query Criteria - Boolean

Question

I'm building a query that I'll use in a form to display a list of employees. On my form I have two comboboxes, one to filter the query by end date and one to filter by status.

The source table for the query has a Boolean field (a Yes/No field) which designates whether the employee is available or not, hence the combo to filter by status. I've run into the issue of how to use non-Boolean combo options but still have the query critera be Boolean.

I know that to use a combobox as a criteria I use this syntax: [Forms]![Form1]![Combo4], but since my combo options are "In Training" and "Available" I don't know how to convert the criteria to Boolean... is this even possible?

Example
If my user selects "In Training" from the combo (which would be equal to False on the source table), my query should use False as the criteria for that field.

After searching Google for an hour without any luck, I'm guessing this may not be possible?

Answer 1

Use an IIf expression to transform the combo's text value to Boolean.

IIf([Forms]![Form1]![Combo4] = "Available", True, False)

Note I assumed you want True when the combo's value is "Available" and False for anything else. If the possibilities are more complex, you could use a Switch expression to assign the correct Boolean for each possible combo value ... or use a lookup table which maps between the two.