Results 1 to 2 of 2
2004-06-25, 15:42 #1
- Join Date
- Nov 2001
- Thanked 0 Times in 0 Posts
ComboBox to 'search for any or all' (Access97)
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.
2004-06-25, 15:54 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: ComboBox to 'search for any or all' (Access97)
You don't really need a fake entry, you can create a union query to add an "All Customers" item:
SELECT "(All Customers)" As Customer, 1 As SortBy FROM tblCustomers
SELECT Customer, 2 FROM tblCustomers
ORDER BY SortBy
You will still need to handle the situation that the user selects the "All Customers" item separately.