Using Query Parameters

So far you’ve been entering selection criteria directly in the design grid of the Query window in Design view. However, you don’t have to decide at the time you design the query exactly what value you want Access to search for. Instead, you can include a parameter in the query, and Access will prompt you for the criteria each time the query runs.

To include a parameter, you enter a name or a phrase enclosed in brackets ([ ]) in the Criteria row instead of entering a value. What you enclose in brackets becomes the name by which Access knows your parameter. Access displays this name in a dialog box when you run the query, so you should enter a phrase that accurately describes what you want. You can enter several parameters in a single query, so each parameter name must be unique as well as informative. If you want a parameter value to also appear as output in the query, you can enter the parameter name in the Field row of an empty column.

Let’s say you’re the housing manager, and you want to find out who might be staying in any facility over the next several days or weeks. You don’t want to have to build or modify a query each time you want to search the database for upcoming reservations. So, you ask your database developer to provide you with a way to dynamically enter the beginning and ending dates of interest.

Let’s build a query to help out the housing manager. Start a new query with tblFacilities in the HousingDataCopy.accdb database. Add tblReservations and tblEmployees. From tblReservations, include the ReservationID, RoomNumber, CheckInDate, CheckOutDate, and TotalCharge fields. Insert the FacilityName field from tblFacilities between ReservationID and RoomNumber. Add an expression to display the employee name in a field inserted between ReservationID and FacilityName. Your expression might look like this:

 EmpName: tblEmployees.LastName & ", " & tblEmployees.FirstName

Now comes the tricky part. You want the query to ask the housing manager for the range of dates of interest. Your query needs to find the reservation rows that show who is in which rooms between a pair of dates. If you remember from the previous example in this chapter where we were looking for employees occupying rooms in June or July, you want any rows where the check-in date is less than or equal to the end date of interest, and the check-out date is greater than the start date of interest. (If they check out on the beginning date of the range, they’re not staying in the room that night.) So, you can create two parameters on the Criteria line to accomplish this. Under CheckInDate, enter: <=[Enter End Date:], and under CheckOutDate, enter: >[Enter Start Date:]. Your query should look like Figure 8–37.

Figure 8–37: You can use query parameters to accept criteria for a range of reservation dates.

For each parameter in a query, you should tell Access what data type to expect. Access uses this information to validate the value entered. For example, if you define a parameter as a number, Access won’t accept alphabetic characters in the parameter value. Likewise, if you define a parameter as a Date/Time data type, Access won’t accept anything but a valid date or time value in the parameter prompt. (See Figure 8–39.) By default, Access assigns the Text data type to query parameters. In general, you should always define the data type of your parameters, so click the Parameters command in the Show/Hide group of the Design tab. Access then displays the Query Parameters dialog box, as shown in Figure 8–38.

Figure 8–39: The Enter Parameter Value dialog box asks for the query parameter value.

Figure 8–38: Use the Query Parameters dialog box to assign data types for query parameters.

In the Parameter column, enter each parameter name exactly as you entered it in the design grid. If your parameter name includes no spaces or special characters, you can omit the brackets. (In this case, your parameters include both spaces and the colon character-either of which would require the brackets.) In the Data Type column, select the appropriate data type from the drop-down list. Click OK when you finish defining all your parameters.

When you run the query, Access prompts you for an appropriate value for each parameter, one at a time, with a dialog box like the one shown in Figure 8–39. Because Access displays the “name” of the parameter that you provided in the design grid, you can see why naming the parameter with a useful phrase can help you enter the correct value later. If you enter a value that does not match the data type you specified, Access displays an error message and gives you a chance to try again. You can also click Cancel to abort running the query. If you click OK without typing a value, Access returns a Null value for the parameter to the query.

Notice that Access accepts any value that it can recognize as a date/time, such as a long date or short date format. If you respond to the query parameter prompts with May 1, 2007, for the Start Date and May 12, 2007, for the End Date, you’ll see a datasheet like Figure 8–40.

Figure 8–40: This figure displays the recordset of the query shown in Figure 8–37 when you reply with May 1, 2007, and May 12, 2007, to the parameter prompts.

You can find this query saved in the sample database as qxmplReservationLookupParameter.