How to use List boxes to send criteria to a query in one easy step...

Many applications use combo boxes or list boxes to supply criteria to a query. Using a combo box is easy, as it only supplies ONE 'result' - but sometimes, a multi-select list box is very handy - selecting one or more values and 'or-ing' them together as the criteria:

..where FOO = "1" or "2" or "3" or "4"...etc etc.

But what if you have two or more list boxes you'd like to use as sources for query parameter? You'd probably end up writing an awful lot of code to handle all the possibilities, but there's a much easier way.

Here's the short explanation:
Design temporary tables to hold the values that you select from your listbox(es). Write a query that JOINS this table to your operational table. Then call that query as your record source.


Here's the longer explanation:

Assume you have a table with a field like "STATE". You'd like to present a list box with all 50 states and allow the user to select one or more of them for your report.

Create a temporary table, with just one field, 'tmpState'.

After your list box selections are made, use a small chunk of code to take the selected items, and insert them in to your temporary state table. For example, if the user selects "CA", "AZ", "WY" and "FL", the temporary table would have four records. The code to enumerate the items selected in multi-select list boxes can be found in various locations around the web, so I won't include it here.

Meanwhile, create a query that joins the temporary state table to your main table, via the STATE field. (the temporary table is the ONE side, the main table the MANY side).

Use whatever other query options you like, and use this query to source your form or report.

With two or more list boxes, just do the same thing - create a temporary table for each list box, and add the temporary table(s) to your query.

There are just a few things to consider as you do this:

For example, can the user NOT make a selection from a list box, and if so, how do you treat that? Do you want ALL or NONE of the items used as criteria (e.g., simply ignore it as a criteria)

If you have two list boxes, and the user may or may not make a selection from each, you will end up with four possible query combinations, thus four potential queries:

1) selection(s) made from BOTH list boxes
2) selection(s) made from First list box
3) selection(s) made from Second list box
4) NO selection made from EITHER list box.

The first scenario will have a query with BOTH temporary tables joined in , the #2 and #3 scenarios will use the appropriate table, alone, and the fourth example will NOT use EITHER temporary table (it's assumed you don't want ANY criteria used.)

This is a very simple technique that allows you to use multiple multi-select list boxes for query criteria, with hardly any programming code at all.