Using Parameter Queries | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

Select and action queries use specific criteria to limit or restrict the records that the query returns. Parameter queries are more dynamic, prompting you for the criteria before the query is run. For example, you could use one parameter query to view sales totals for different regions, instead of having to write a separate select query for each region. To create a parameter query, you enter a parameter expression (enclosed in brackets) in the Criteria cell of a query column, instead of entering the specific criteria. When you run the query, Access displays a parameter prompt that contains the text of the parameter expression. After you enter data in response to the prompt, Access uses the data you enter as the query’s criteria. The example in this section returns customers based on the country in which they reside.

note

The examples in this chapter work with the tables in the Northwind database that comes with Access. If you don’t want to actually alter these tables, be sure to work with copies of the tables or a copy of the database. For instructions on how to copy a table, refer to Chapter 10, "Using Action Queries."

To create a parameter query, follow these steps:

Select the Customers table in the Database window, and then select Query from the New Object button’s drop-down list. The New Query dialog box will appear, with the Design View option highlighted. Click OK in the New Query dialog box.
Drag the CompanyName and Country fields to the design grid.
In the Country field’s Criteria cell, enter the following expression, as shown in Figure 11-1:
```
 [Enter a Country] 
```
Be sure to include the square brackets.

Figure 11-1. Enter a parameter expression in the Country field’s Criteria cell.
Click Run on the Query Design toolbar, or choose Datasheet View from the View button’s drop-down list. Access will display a parameter prompt.
Enter a country name. (The example in Figure 11-2 uses Germany.) Click OK.
Figure 11-2. Access displays a parameter prompt when you run the parameter query.

The results will include only those records that include the string "Germany" in the Country field, as shown in Figure 11-3.

figure 11-3. this query returns records for customers in germany.

Figure 11-3. This query returns records for customers in Germany.

Choose SQL View from the View button’s drop-down list to view the parameter query’s SQL equivalent, shown here:

 SELECT Customers.CompanyName, Customers.Country FROM Customers WHERE (((Customers.Country)=[Enter a country]));

This query is really just a select query because it’s retrieving data. It specifies the fields and the table from which the data will be retrieved. The query even restricts that data with a WHERE clause. The difference is the form of the WHERE clause. Specifically, [Enter a country], to the right of the equal sign, refers to the parameter expression instead of a literal value.

Troubleshooting - When I run a select query, I’m prompted for a parameter value, but I’m sure I’m not running a parameter query

Sometimes a select query displays an inappropriate parameter prompt when you’re not working with a parameter query. This error almost always points to a typo in a field name. To fix the problem, try these solutions:

Open the query, and make sure that all references to fields are spelled correctly.
Check any fields you recently renamed at the table level. Renaming can cause a problem in criteria expressions.
If you still can’t find the problem, open the data source, check all the field names, and then recheck your query.

Prompting for Dates

A common use of parameters is to prompt the user for a specific time period. When you need the user to specify a time period, you combine parameter expressions with the Between and And operators in the following manner:

 Between [Enter the beginning date] And [Enter the ending date]

Access will then prompt you for two dates—the first and the last in the time period in question.

For example, to create a parameter query to view orders required in August 1996, follow these steps:

Select the Orders table in the Database window, select Query from the New Object button’s drop-down list, choose Design View, and then click OK in the New Query dialog box.
Drag the OrderID, CustomerID, EmployeeID, and RequiredDate fields to the design grid.
Select the Criteria cell in the RequiredDate column, and enter the following expression (see Figure 11-4):
```
 Between [Enter the beginning date] And [Enter ending date] 
```
Figure 11-4. Combine operators and parameter expressions to match a specific time period.
Click Run, and Access will display the parameter prompt. Enter the date 8/1/96, as shown in Figure 11-5, and click OK.
Figure 11-5. Enter the first date in the time period you’re matching.
When Access displays the second parameter prompt, enter the date 8/31/96, as shown in Figure 11-6, and then click OK.

Figure 11-6 - Enter the last date in the time period you’re searching for.

The results, shown in Figure 11-7, contain only those records with an August 1996 date in the RequiredDate field.

Figure 11-7. This parameter query matches only August 1996 dates in the RequiredDate field.

tip

Expressions can be long and complex. When you enter an expression in the Criteria cell, press Shift+F2 to open the expression in the Zoom window, where you can view and edit the entire expression. You can also select Build from the shortcut menu to open the Expression Builder for help in constructing the expression with correct syntax.

Setting Data Types

The query in the previous section prompted you for dates. If you enter anything but a valid date, Access might accept your entry and return erroneous data. You can restrict the user from entering invalid data by specifying a parameter’s data type. By specifying the valid data types, you help eliminate some data entry problems.

For example, to make sure that employees are keeping up with their orders, follow these steps. If you try to look up the employee’s order records but enter incorrect data (such as the wrong employee ID) in response to the employee parameter prompt, you won’t get the data you want and you won’t know there is a problem.

Select the Orders table in the Database window, choose Query from the New Object button’s drop-down list, choose Design View, and click OK in the New Query dialog box.
Drag the following fields to the design grid: OrderID, CustomerID, EmployeeID, RequiredDate, and ShippedDate.
Enter the following expression in the EmployeeID field’s Criteria cell:
```
 [Enter employee’s ID] 
```
Enter the following expression in the ShippedDate field’s Criteria cell, as shown in Figure 11-8:
```
 >[RequiredDate] 
```
Figure 11-8. This query will prompt users to identify the employee.
Click Run, and enter a valid employee identification value in response to the parameter prompt. In this case, enter the value 1, as shown in Figure 11-9, and click OK.
Figure 11-9. Enter a valid employee identification value.

The results, shown in Figure 11-10, include only those records that belong to the employee identified in the earlier prompt. In addition, the results are limited to only those records in which the ShippedDate value is later than the RequiredDate value, meaning that the order was shipped late.

figure 11-10. the results show records that belong to nancy davolio, whose employee id is 1.

Figure 11-10. The results show records that belong to Nancy Davolio, whose Employee ID is 1.

InsideOut

Many developers avoid using lookup fields because of the confusion they can cause. For instance, in the current example you must enter the employee’s EmployeeID value (or primary key value) although the field displays the employee’s name. Northwind makes extensive use of the lookup field feature; becoming familiar with the feature can save you a lot of troubleshooting if you should ever acquire a database that uses lookup fields.

Run the query a second time and enter Nancy Davolio in response to the query’s prompt. This time the query returns an empty result set, but you already know that Nancy Davolio has two late orders from the previous example. (Remember that theEmployeeID field actually contains a numeric value that represents the employee ID, not the employee’s name. This field displays each employee’s name because it’s a lookup field.) You can avoid some data entry errors by setting a parameter’s data type. If you limit the parameter to accept only Byte values, the query will return an error if you enter anything but the values 1 through 255.

tip

You don’t have to return to Design view and click Run to rerun the same query. Simply press Shift+F9. If you’re running a parameter query, Access will display the parameter prompt.

To set the parameter’s data type, follow these steps:

In Design view, highlight and copy (by pressing Ctrl+C) the following parameter expression in the EmployeeID’s Criteria field:
```
 [Enter Employee’s ID] 
```
Choose Query, Parameters.
Select the first Parameter field and press Ctrl+V to paste the copied parameter expression.
Press Tab, and choose Byte from the Data Type field, as shown in Figure 11-11. Click OK to close the Query Parameters dialog box.
Figure 11-11. Specify a data type for the parameter.
Click Run, enter Nancy Davolio, and then click OK. This time, Access displays the error message shown in Figure 11-12 in response. The message isn’t terribly specific, but it’s better than an empty record set and no warning at all.
Figure 11-12. Access displays an error message if a parameter isn’t the right data type.

Planning for Null Parameters

Parameter queries have one peculiar, unexpected behavior that can be a bit confusing to remedy if you don’t understand the cause. You might think that not responding to the parameter would return all the underlying records. After all, by not responding, aren’t you eliminating the criteria altogether? Unfortunately, Access doesn’t respond the way you might think. Instead of returning all the underlying records, it returns no records. This happens because Access interprets the empty parameter as a Null value. Because the entries in the table aren’t Null, they don’t match the parameter value, and so they aren’t returned.

You can avoid this situation by adding a Like operator to the parameter expression in the following form:

 Like [parameter] & "*"

If you enter this parameter, Access tacks on an asterisk (*). The asterisk is a wildcard character that matches other characters. For example, if you enter London, Access will search for London *. If you enter nothing, Access searches for *, which of course, returns all the records. There’s one more benefit: You can enter just the first few characters instead of the entire entry. For instance, you could enter Lon instead of London. Doing so will return all entries that begin with Lon, not just London, so this solution might not always be appropriate.

To plan for Null values in a parameter expression, follow these steps:

Open the Suppliers table, and delete the city name (London in this example) from the first record’s City field. Save this change.
Base a query on the Suppliers table, and drag the following fields to the design grid: SupplierID, CompanyName, ContactName, and City.
Enter the following expression in the City field’s Criteria cell:
```
 [Enter City] 
```
Run the query, and enter New Orleans at the prompt to return any records in which the City field contains the string "New Orleans". (This query should return just one record.)
Press Shift+F9 to run the query again. This time, click OK without responding to the prompt. Access will return an empty recordset.
Return to Design view, and modify the City’s Criteria expression to look like this (see Figure 11-13):
```
 Like [Enter City] & "*" 
```
Figure 11-13. Add a Like operator to the parameter expression.
Run the query again, and click OK instead of entering a parameter value.

This time Access returns all the records. Or does it?

Did you notice that the query didn’t return the record with the missing City value? (You deleted London from the first supplier record.) A Null parameter, in this context, won’t select a Null value. You can remedy this problem quickly enough by adding an Or expression in the following form, as shown in Figure 11-14:

 [parameter] Is Null

figure 11-14. an additional parameter expression returns null values.

Figure 11-14. An additional parameter expression returns Null values.

Simply open the parameter query in Design view, and add the additional parameter expression to the City field’s Or cell. Then run the query again, and click OK without entering a parameter value. This time the query returns all the records, including the first record with the missing City value, as shown in Figure 11-15.

Figure 11-15. After you add the </i>Is Null<i> expression, the results include Null values.

Figure 11-15. After you add the Is Null expression, the results include Null values.

An Alternative to a Parameter Query

Earlier you set a parameter’s data type to limit the type of data you can enter as that parameter’s value. (See the section "Setting Data Types.") Doing so isn’t thesame as setting a field’s ValidationRule property, but these techniques have similar purposes. You can limit parameters only by data type, whereas you can use a validation rule to limit values to a specific range. In the employee ID example, it would be helpful to limit the parameter to accept only those employee IDs that actually exist, but you can’t.

For more information about the ValidationRule property, see Chapter 4, "Creating a Database."

When restricting the values that a user can enter for a query is critical, you might need another solution. The simplest solution is to replace the parameter query with a form and a nonparameter query. The form offers the user a list of specific choices, usually in a combo box or list box control, and then passes the chosen items to the query. Let’s try a simple form and query solution to replace our employee ID example.

For the following example, refer to the Test Access 2002.mdb file on the companion CD.

To create this query, follow these steps:

Select the Orders table in the Database window, select Query from the New Object button’s drop-down list, make sure that Design View is selected, and click OK in the New Query dialog box.
Drag the following fields to the design grid: OrderID, CustomerID, EmployeeID, RequiredDate, and ShippedDate.
Enter the following expression in the EmployeeID field’s Criteria cell:
```
 FindEmployee() 
```
note

The FindEmployee function is discussed a little later in this section.
Enter the following expression in the ShippedDate field’s Criteria cell (see Figure 11-16):
```
 >[RequiredDate] 
```
Figure 11-16. This query calls the FindEmployee function instead of a parameter expression.
Save the query as qryFindEmployee, and then close it.

To create the parameter form, follow these steps:

note

The following procedure assumes some familiarity with writing code in Microsoft Visual Basic for Applications (VBA). If you aren’t familiar with VBA, you might want to first read Chapter 20, "Customizing Your Database Using VBA Code."

In the Database window, click Forms in the Objects list, click New on the Database Window toolbar, make sure Design View is selected, and then click OK in the New Form dialog box.
Add a list box to the form, and name it lstFindEmployee.
Enter the following SQL statement as the list box control’s Row Source property:
```
 SELECT DISTINCT Orders.EmployeeID, Employees.LastName, Employees.FirstName FROM Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID; 
```
This statement displays a unique list of entries from the EmployeeID field in the Orders table and the corresponding last and first names for each of those values. To make it easier to enter the SQL statement (it is long), press Shift+F2 to open the Zoom window. Enter the SQL statement, and then close the window.
Change the Column Count property to 3.
Enter the Column Widths setting of 0";1";1", as shown in Figure 11-17.
Figure 11-17. The properties sheet shows the list box control’s property settings.

This setting hides the EmployeeID field, so all you see is the last and first names.
Add two command buttons. Name the first button cmdFindEmployee and the second button cmdCancel. Set the caption for the first button to Find Employee and the caption for the second button to Cancel.
Save the form as frmParameterForm.
Launch the Visual Basic Editor (VBE) by clicking Code on the Form Design toolbar or by pressing Alt+F11.
If the form’s module isn’t open, double-click Form_frmParameterForm under the Microsoft Access Class Objects mode in the Project Explorer.
In the form’s module, select cmdFindEmployee in the Object control’s drop-down list. You’ll find this control to the left, just above the module under the module window’s title bar, which you might not be able to see. If this control isn’t visible, click the window’s Restore button to display the window’s title bar.
Add the Option Explicit statement, and enter the following VBA statement between the subprocedure’s opening and closing statements, as shown in Figure 11-18:
```
 DoCmd.OpenQuery "qryFindEmployee" 
```
Figure 11-18. Enter the VBA code that runs the query.

This statement will execute the query you just created.
Insert a standard module by choosing Insert, Module.
Enter the function procedure shown in Figure 11-19.
Figure 11-19. This function procedure passes the selected item in the list box to the parameter.
Save the module as basFindEmployee. The function procedure you just created (also known as a user-defined function) returns the selected item in the list box control.
Return to the form in Design view, and click View to display the form.
Select the first item, Nancy Davolio, as shown in Figure 11-20, and then click Find Employee to execute qryFindEmployee.
Figure 11-20. Select an item from the list box.

The query then calls the FindEmployee function, which equals the selected item in the list box control in frmParameterForm. As a result, the query uses that value as criteria and displays only those records for Nancy Davolio in which the ShippedDate is greater than the RequiredDate. (The results should be identical to those shown earlier in Figure 11-10.)

For additional help in creating this form, see Chapter 5, "Creating Forms for Entering, Editing, and Viewing Data."

As you’ve seen, setting up the form to limit user input requires quite a bit of additional work. Do the advantages warrant the extra work? More than likely, yes. A user can easily enter an incorrect parameter value or misspell an entry. As a result of this mistake, the parameter query returns erroneous data or no data instead of an error message. Using a parameter form avoids simple typos and prevents user frustration by limiting the user to a list of valid parameters.

Assigning Default Values to a Parameter Query

As you just learned, parameter queries don’t warn you when you enter incorrect data in response to a parameter prompt. They have another limitation: You can’t assign a default value for the parameter. A default value is useful when you find yourself entering one value more than any other in response to a parameter prompt. You can avoid entering a response at all except on those few occasions when you need a value other than the default. Needing a default value is another good reason for using the alternative described in the previous section. To specify a default value, open the parameter form’s module (from the form you created in the last example), and add the following code to the form’s Current event (where x represents the index value of the list item you want to specify as the default parameter value):

 Private Sub Form_Current()     Dim ctl As ListBox     Set ctl = Forms!frmParameterForm!lstFindEmployee     ctl.DefaultValue = ctl.ItemData(x ) End Sub

For instance, if you wanted "Margaret Peacock" to be the default, you’d use the index value 3. (Indexing begins with the value 0.)

InsideOut

If you based a report on a parameter query, you might want to print the parameter values in the report. For example, if the parameter is a date, you could display that date in the report’s title or header. To do so, simply add a text box to your report’s header or title and specify the parameter by name as the control’s Control Source using the following form:

 =Reports![reportname]![parametername]

Suppose you want to add the feature to a report named rptDates and the actual parameter is [Please Enter Date]. In this case, you’d enter the following expression:

 =Reports![rptDates]![Please Enter Date]

Now suppose that you run the query and enter the date August 15, 2001. As a result, the text box that contains the reference to the parameter displays and prints August 15, 2001.