Multiple Combo Boxes as Criteria for A Query
A popular way to let end users search for information is to
provide a form with many combo boxes on it which have drop down lists of data
from a table or query, then to base a query on the values in those combo boxes.
The one stumbling point that everyone comes across is when the users don't want
to select a value from every combo box. This was very simple to remedy until the
point where the query builder fails to recognize complex logical operations, so
it's time to use the SQL text part of the query builder. All sounds complex but
don't panic.......
Imagine we have a table with customers in (Take northwind.mdb)
as an example. We want to be able to search for customers by city, state or
both.
Build a form not bound to anything and place two combo boxes on
it. One called city which as it's data source contains all the cities in the
Customer Table and the other called country which as it's data source contains
all the countries from the customer table.
Save the form as SearchForm.
Create a new query and bring in the customers table. Drag the
city and country fields to the design grid and any other fields you want to
see.
Then we go into the scary bit, where the view button is on the
toolbar click the little down arrow and select SQL View
You will see a bunch of text something along the lines of
SELECT City, Country
FROM CustomerTable |
Yours will obviously reflect your field names. Next we enter the
complex bit at the end of this text paste the following. Not forgetting to
replace the words City and Country for your field names ....
| WHERE (((City)=[Forms]![SearchForm]![City] Or
IsNull([Forms]![SearchForm]![City])) AND ((Country)=[Forms]![SearchForm]![Country] Or
(IsNull([Forms]![SearchForm]![Country])))); |
Okay go back into design view and you will see four lines of
criteria in your query. Take a little time and you will see it has worked
out the four cases for you.
- 1. City = formCity and Country = formCountry
- 2. City = formCity and formCountry IS NULL (contains nothing)
- 3. formCity IS NULL and COuntry = formCountry
- 4. formCity IS NULL and formCountry IS NULL
Go back into the SQL and you will notice it is no longer the
same. Don't panic, access has changed it into something it can understand better
and calculate faster.
This will allow the user to select either a city or country or
both and get records back matching their criteria. Remember the form must be
open when you run the query. So it's best to run the query from a button on the
form, or my favourite is to create a sub form on the search form that is based
on the query and then a button which all it does is
which will refresh the records.
You just learnt your first bit of SQL. That wasn't so hard was
it?
| 