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
To create a parameter query, follow these steps:
[Enter a Country]
Be sure to include the square brackets.
Figure 11-1. Enter a parameter expression in the Country field’s Criteria cell.
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.
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:
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:
Between [Enter the beginning date] And [Enter ending date]
Figure 11-4. Combine operators and parameter expressions to match a specific time period.
Figure 11-5. Enter the first date in the time period you’re matching.
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
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.
[Enter employee’s ID]
>[RequiredDate]
Figure 11-8. This query will prompt users to identify the employee.
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.
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
To set the parameter’s data type, follow these steps:
[Enter Employee’s ID]
Figure 11-11. Specify a data type for the parameter.
Figure 11-12. Access displays an error message if a parameter isn’t the right data type.
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:
[Enter City]
Like [Enter City] & "*"
Figure 11-13. Add a Like operator to the parameter expression.
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.
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 Is Null expression, the results include Null values.
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:
FindEmployee()
note
>[RequiredDate]
Figure 11-16. This query calls the FindEmployee function instead of a parameter expression.
To create the parameter form, follow these steps:
note
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.
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.
DoCmd.OpenQuery "qryFindEmployee"
Figure 11-18. Enter the VBA code that runs the query.
This statement will execute the query you just created.
Figure 11-19. This function procedure passes the selected item in the list box to the parameter.
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.
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.