I have a form that displays a table of “orders” that has a field for “customer”. I want to add a filter with a combo-box listing all the customers, so that the filter displays all the orders for the selected customer. I can do this by populating the combo-box from the “customers” table or a query that extracts them from the “orders” table. And I can turn the combo-box selection into a “WHERE” clause for a query or a filter.
But what I’d like is an additional option in the combo-box for “all customers”, and so far I’ve only thought of slightly clunky ways to do this, with a fake entry for “all customers” in the customers table, and code in one of the combo-boxes events that checks for this being chosen, and generates a different query or filter as a special case. Has anyone come up with a neater way to do this? Many databases seem to have this (including some of the Access Wizards), where you get a list of, say, countries, and the first entry is “all countries”, so it must be a frequently asked question. Also, the last time I tried this, the users couldn’t tell whether they were choosing “customer = any” or “customer = null”, which could be the case in this database because you’re allowed to have incomplete entries.
