How to select All Records from a Query based on what is entered in a combo box control on Form

0
votes

I have a query called qryFlightPrices. It contains four fields. One of these fields is called Favs.

In the Favs field of this Query I have entered filter Criteria which references a Combo Control on a Dialog form. I entered the criteria for the Favs field like this [Forms]![frmFlightPrices]![Favs]

The combo controls row source is this code:

SELECT DISTINCT AirportRoutes.Favs 
FROM AirportRoutes; 
UNION Select "<All>" From AirportRoutes
ORDER BY Favs;

From the combo box drop down menu I have these three options:

All,

Holiday Only,

For Relocation

The query works when I select the Holiday Only and For Relocation options

The Problem

When I select All from the combo box in order to see all records from the Favs field the query doesnt return any results.

When I leave the combo blank the query also returns no results.

How can I get the query to return all results

Thank you

1
formsms-accesscombobox

1 Answers

2
votes

Change the WHERE clause of your query qryFlightPrices from:

[Favs] = [Forms]![frmFlightPrices]![Favs]

to:

1 = iif([Forms]![frmFlightPrices]![Favs] = "<All>", 1, iif([Favs] = [Forms]![frmFlightPrices]![Favs], 1, 0))

If I misinterpreted your WHERE clause, please supply the query, so we can work on it directly.