November 1999

Working with Multiple Query Criteria for a Single Field

by Susan Sales Harkins

Suppose you want to query a table of seminar attendees by city. However, you're not interested in learning just about the attendees for a seminar in one city--you want to return the names of the attendees for two or more cities. The simplest way to return the right records would be to base a query on the attendee table and specify the cities in question as Or criteria. If you want to reuse the query for different city combinations, you could simply create a parameter query, right? Wrong; you can't send multiple criteria to your query via the same parameter prompt.

Our solution

Perhaps the safest way to protect the integrity of your query and still maintain flexibility is to use a parameter form--a form that allows you to choose from a list of items. In other words, instead of typing a parameter in response to a prompt, you'll choose one or more items from a comprehensive list, like the one shown in Figure A. Once you make your selections, underlying code will run your query (or other tasks); you'll never see a query or respond to a parameter prompt.

Figure A: This is the parameter form we'll use to pass criteria to our report.

This solution requires that we build a complex SQL string on the fly. The advantage is that it's dynamic--you can pass one city or all the cities to the report via the WHERE clause. In addition, it requires only one extra component--the parameter form. This solution doesn't require a query at all.

You may also see similar solutions that use function procedures. This technique is simple enough that we won't need an additional module. All the code we need will be in the form's module.

Creating the table and report

We'll set up a basic table for our example and ignore most of the fields you'd likely have. Create a table called tblAttendees, using Table A as a guide. Use AttendeeID as the Primary Key for the database. When you've created the database, populate it with the data shown in Figure B.

Table A: The tblAttendees structure

Field Name Data Type Caption
AttendeeID AutoNumber Attendee ID
strFirstName Text First Name
strLastName Text Last Name
strSeminarCity Text Seminar Location

Figure B: Use this data to populate the sample table.

The report, shown in Figure C, is the simplest piece of our solution. We'll simply base a tabular report on the tblAttendees table. To do so, select tblAttendees in the Database window and then choose Report from the New Object button's dropdown list. In the New Report dialog box, double-click on Report Wizard.

Figure C: We'll use a parameter form to limit the records displayed in this report.

In the wizard's first window, add AttendeeID, strFirstName, strLastName, and strSeminarCity to the Selected Fields control, and then click Next twice.

Now, you'll set the sort order for the report. First, choose strSeminarCity from the first dropdown list. Then, select strLastName and strFirstName from the second and third dropdown lists, respectively. When you've finished, click Next.

On this screen of the wizard, select Tabular in the Layout options and click Next twice. Name the report rptAttendees, and click Finish. (Note that in Figure C we changed the report's title to Attendees by city in Design view, but doing so isn't critical to the technique.) Once you've created the report, save and close it; we won't need it for a while.

Creating the parameter form

A parameter form is an unbound form that passes a single choice or a series of choices to another object. That object can be a module, a query, or a report, such as our rptAttendees. To build the form shown in Figure A, we'll bind a listbox control to the strSeminarCity field in our table, and then add the necessary code to the command button's Click event. The first step to building this form is to open a blank form and add a listbox. To get started, click on the Forms tab in the Database window and then click New. In the New Forms dialog box, double-click on Design View. If the Toolbox isn't open, click the Toolbox button on the Form Design toolbar. If the Control Wizard button is selected on the Toolbox, deselect it now.

Adding the listbox

With the blank form in Design view and the Toolbox open, add a listbox to the blank form and delete the control's label. Double-click on the listbox to open its property sheet. Then, change the listbox's Name property to lstCity. Displaying the cities in the listbox is a simple task--we'll set the control's Row Source property to an SQL statement. To do so, choose tblAttendees from the Row Source property's dropdown list. Then, click the field's Build button, and click Yes to continue.

Now, drag the strSeminarCity field from the field listbox to the first cell in the query design grid. In its present state, the query will return an entire list of all the cities in the strSeminarCity field. That means some cities may be duplicated in our list. We want our list to display each city only once. Fortunately, all we need is one small modification to the query's SQL statement. Choose SQL View from the View button's dropdown list and Access will display the SQL statement 

SELECT tblAttendees.strSeminarCity
FROM tblAttendees;

Access 95 users will also see the DISTINCTROW key word in the SQL statement. At this point, regardless of version you're using, change the statement to 

SELECT DISTINCT tblAttendees.strSeminarCity
FROM tblAttendees;

Once you've altered the statement, close the SQL Statement window and click Yes when prompted to save your changes. Finally, set the Multi Select property to Simple. That will allow you to choose more than one item in the listbox. Now would be a good time to save your form. We named ours frmParameter.

Adding the command button

Our second control is a simple command button. Add that control right below lstCity. Open its property sheet, name it cmdOpen, and then change its caption to Open Report. At this point, save your form again. Adding the event procedure

Now that we've assembled the pieces, we're ready to add the event procedure that will base our report on the selections made in lstCity. To open the form's module, simply click the Code button on the Form Design toolbar. In the resulting module, choose cmdOpen from the Object dropdown list and Click from the Procedure dropdown list. Then, add the procedure shown in Listing A. Compile the code by clicking the Compile Loaded Modules button on the Visual Basic toolbar. Finally, save and close the module.

Note: Although you can include line numbers in the main body of the code, don't include the line numbers for the Sub and End Sub statements. Also, Access 95 users must substitute the constant acPreview for acViewPreview in line 25.

Listing A: The Click procedure 


1 Private Sub cmdOpen_Click()
2 Dim frm As Form, ctl As ListBox, var As Variant
3 Dim strCriteria As String, temp As String
4
5 Set frm = Forms!frmParameter
6 Set ctl = frm!lstCity
7
8 'If no selection, display warning and exit
9 If ctl.ItemsSelected.Count = 0 Then
10    MsgBox "Please select a city."
11   Exit Sub
12 'builds SQL WHERE clause
13 'using each of the selected cities
14 Else
15   For Each var In ctl.ItemsSelected
16    temp = "[strSeminarCity] = " & Chr(39) & _
				ctl.ItemData(var) & 
Chr(39) & " Or "
17    strCriteria = strCriteria & temp
18    Next var
19 End If
20
21 'deletes the final Or from the WHERE clause
22 strCriteria = Left$(strCriteria, Len(strCriteria) - 4)
23
24 'opens report, closes form
25 DoCmd.OpenReport "rptAttendees", acViewPreview, , 
strCriteria
26 DoCmd.Close acForm, "frmParameter"
27
28 Set ctl = Nothing
29 Set frm = Nothing
30 End Sub
As usual, the first few lines of code declare and define variables. The If condition checks lstCity's current selection. If no item has been chosen, the True action displays a message box asking you to select a city and then exits the procedure. This quick check avoids a runtime error when there's no selection.

When lstCity returns a selection, the Else condition executes the For loop that builds our SQL WHERE clause. This loop executes once for each selected item in lstCity.

The first statement in the For Each statement 

temp = "[strSeminarCity] = " & Chr(39) & 
	ctl.ItemData(var) & Chr(39) & " Or "
concatenates the first part of the WHERE clause--the field we're searching--with the first entry we're searching for. In other words, if the first item selected in lstCity is London, this statement evaluates to 
[strSeminarCity] = `London' Or
The next statement has no affect during the first loop. However, let's assume you've selected London and Seattle. The first time through the loop, strCriteria equals the previous statement. The second time through the loop, temp evaluates to 

[strSeminarCity] = `Seattle' Or
and strCriteria evaluates to 
[strSeminarCity] = `London' Or [strSeminarCity] = `Seattle' Or
Each loop adds an additional field search to the WHERE clause. We just happen to be adding the same field. This setup leaves us with an extra Or at the end of our statement. So, the next statement deletes the last Or from our clause. After doing so, strCriteria equals a valid WHERE clause, such as 

[strSeminarCity] = `London' Or 
		[strSeminarCity] = `Seattle'

The first DoCmd statement opens rptCity using strCriteria as its WHERE clause argument. In this case, rptAttendees will open with only those records that contain London or Seattle in the strSeminarCity field. The second DoCmd statement simply closes our parameter form. The last two statements set our two object variables to nothing.

Running the form

We're finally ready to run our parameter form. Open the form in Form view and click the Open Report command button without selecting an item. Doing so displays the message shown in Figure D. Click OK to clear the message.

Figure D: You must select a city from the list to open the report.

That message is the result of the If condition we discussed earlier. The condition 

Ctl.ItemsSelected.Count = 0
is True, so VBA displays the message box and exits the procedure. Let's try again--select Boston and Seattle, and then click the Open Report command button. The report now displays only those records that contain either Boston or Seattle in the strSeminarCity field. You can also choose just one city, or all the cities.

Another alternative

As is almost always the case with Access and VBA, there's more than one way to approach a problem. We used the Or operator to build the SQL WHERE clause because it's probably the most often used solution. However, you can also use the In operator. Your final clause will contain fewer characters, which is always a plus. In addition, it's easier to read because you eliminate all the field references. If you'd like to try the In operator in our sample form, replace lines 16, 22, and 23 in Listing A with the following statements: 

16 temp = Chr(39) & ctl.ItemData(var) 
		& Chr(39) & ","
22 strCriteria = Left$(strCriteria, 
		Len(strCriteria) - 1)
23 strCriteria = "[strSeminarCity] In 
		(" & strCriteria & ")"
Conclusion

When you need to query a single field for multiple items, it may be most efficient to use a form to set your search criteria. In this article, we've shown you how to build a flexible SQL query using a form with a listbox. Your query will meet your needs for finding one or several criteria from the same field.

Copyright © 1999, ZD Inc. All rights reserved. ZD Journals and the ZD Journals logo are trademarks of ZD Inc. Reproduction in whole or in part in any form or medium without express written permission of ZD Inc. is prohibited. All other product names and logos are trademarks or registered trademarks of their respective owners.