Microsoft Access Office VB VBA Help and Examples
           
             

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 

[SubformName].Requery

which will refresh the records.

You just learnt your first bit of SQL. That wasn't so hard was it?

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.