by Sal Ricciardi
Queries and reports provide the means by which we ask questions and get answers from a database. In this article, learn how to use parameters to add flexibility to your queries and reports, and how to supply parameters through a custom-designed form. We provide a downloadable database with examples.
|Microsoft Office Access 2003
Microsoft Access 2002
Question: How do I create a query that lets me alter a criteria value without having to modify my query design every time?
You should use parameters with your query. First let's review this requirement in context. Databases exist for the primary purpose of serving up information: "What's our best-selling product? Who's our best customer? Where are we not meeting our sales targets?" All are questions that you might legitimately ask of a well-designed database. To get the answers from your Access database, you create a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.), choose the appropriate tables, and enter the needed criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.).
Over time, you'll often find yourself asking the same questions, and therefore running many of the same queries repeatedly. Sometimes, however, the question remains the same while the search values, or criteria, change. For instance, you might want to see sales for the Beverages category today, the Condiments category tomorrow, and the Confections category the day after tomorrow (pardon me a moment while I snack on some dark chocolate). This means you must open your query in Design mode, change the criteria to specify Beverages, run the query, change it again to specify Condiments, run the query again, change it again to specify Confections, and so on.
If you frequently run the same query but change the criteria each time, you'll save time by using a query parameter. With a query parameter, you can create a query that asks the user to supply the search value. This way, you can use the query over and over without modifying its design.
Introducing query parameters
A query parameter (often called just a parameter) is a placeholder for an actual value. To use a query parameter, you first open an existing query, or if you don't have one, create a new query and choose your tables and fields. To create the parameter, type a phrase enclosed in brackets in the Criteria row of your query design grid (see the example shown inside the red circle below). When you run the query, Access sees the bracketed parameter and prompts you to enter a value in the Enter Parameter Value dialog box. The value you enter is passed to the query as the parameter. It's as if you typed the value directly into the query design grid — but you didn't have to modify the query.
The text you supply within the brackets of the parameter becomes the prompt that you see in the Enter Parameter Value dialog box, so you should choose your phrase carefully and make sure it clearly indicates the information that needs to be entered. The phrase also serves as the name that Access uses to identify the parameter.
After you enter the value — Confections, for example — Access processes the query, selects the matching data, and presents the results in a datasheet.
If you press ENTER without supplying a value, Access displays an empty datasheet.
Using more than one parameter
A query that contains a parameter is called a parameter query. Sometimes you want a parameter query that prompts for more than one value. For instance, you might want to design a query that lets you summarize product sales. Every time you run the query, you want to enter both the sales category and the start and end date that specify the date range for the sales.
For such a query, you would need three parameters. Access lets you specify parameters in each of several fields; plus, you can specify more than one parameter in a single field. For this example, you would add a parameter to the CategoryName field and two parameters to the OrderDate field. In the OrderDate field, you would use the built-in operator called Between...And in conjunction with the parameters to tell Access to include sales only where the order date falls between the specified start date and end date. The resulting query design grid would then look something like this:
When you run the query, Access prompts you with three successive Enter Parameter Value dialog boxes: one for the category name, one for the start date, and one for the end date.
Using the Query Parameters dialog box
Often, you'll want to have more control over the order in which the Enter Parameter Value dialog boxes appear. For instance, you might want to make sure the Category Name prompt appears before the Enter Start Date and Enter End Date prompts. You can do this by using the Query Parameters dialog box. To open the Query Parameters dialog box, first open your query in Design view, and then, on the Query menu, click Parameters.
The values you enter in the Query Parameters dialog box control two things: first, the order in which to display the parameters, and second, the data type to expect for each parameter. To populate the dialog box, enter the parameters in the order you want them to appear, being careful to enter the text exactly as it appears in the query design grid. You can also select the text in the grid, press CTRL+C to copy, and press CTRL+V to paste the text into the fields of the Query Parameters dialog box. Note that the parameter text is only the text within the brackets; it does not include operators such as Between and And.
When you run your parameter query, Access uses the data types you chose in the Query Parameters dialog box to validate the data that is entered. For instance, if you chose the Date/Time data type and entered 31-Feb-2004 (a date that could never occur), Access displays an error message that says, "The value you entered isn't valid for this field," and you'll have to re-enter the date. When you enter a parameter in the Query Parameters dialog box and don't specify a data type, Access converts the value entered into the Text data type.
Employing a custom form to collect the parameters
One of the more common questions asked about parameters is how to use a form instead of Enter Parameter Value prompts to pass those values to a query. There are a number of reasons why you might want to use a form instead of the Query Parameters dialog box:
- When you have multiple parameters, you will likely find it easier to submit them through a form than through a separate prompt for each parameter. Using multiple Enter Parameter Value prompts means you will have to click OK or press ENTER several times. If instead you use a form, you can simply fill in fields and then click OK or press ENTER once.
- Using a form lets you go back and edit a value that you entered in a field before submitting the form. You can press SHIFT+TAB to move to the previous field and change what you entered without having to start over.
- By using a form, you can choose to use a control (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) more appropriate to the type of parameter you're entering, and make it easier to enter values. For instance, you might use a combo box to choose a Category value instead of having to enter a value in a text box.
- A form lets you attach code written in Visual Basic for Applications (VBA), the programming language that Access uses, so that you can validate data in a more robust way than the simple data type checks performed by Access through the Enter Parameter Value dialog box.
Creating a form to supply parameters to a query
Suppose you want to see a summary of product sales for a specific category and time period. You can do this by combining a form, a query, and a small amount of VBA programming. The form works like a typical Windows dialog box in that it lets you enter and edit its parameters. When you click OK, the form invokes a query specially designed to work in conjunction with the form.
The Sales By Category Summary Query parameters form, shown above and included in the sample database, demonstrates this technique. To create this form, you'll need to take a number of steps. I'll summarize them first, and then, where needed, provide detailed instructions in the Step by Step expandable sections shown below.
To create the Sales By Category Summary Query parameters form
- Create a new form and add the controls needed for entering the parameters.
- Modify your query so that it obtains its parameters from the controls on the form.
- Add an OK button to the form that runs the query and then closes the form.
- Add a Cancel button that closes the form without running the query.
To begin, create a new form in Design view, then carefully select and add the appropriate controls for entering the Category, Start Date, and End Date parameters (see the Step by Step section below for detailed instructions). For the example form, I chose to use a combo box to select the category, and two text boxes for the dates. For the combo box, I chose the Categories table as the row source, so that Access would automatically fill the combo box with all of the categories.
Next, customize the behavior and appearance of the form and its controls by setting properties (property: A named attribute of a control, a field, or an object that you set to define one of the object's characteristics (such as size, color, or screen location) or an aspect of its behavior (such as whether the object is hidden).) where needed. For instance, I changed the form's BorderStyle property to the Dialog value because I wanted the appearance and behavior of the form's borders to be that of a typical Windows dialog box. I also changed the Default Value property value for the Category combo box to Beverages so that the combo box is preloaded with a value.
Here are the step-by-step instructions:
Step by Step: Create a new form and add the controls for entering the parameters
To create the form
- In the Database window, under Objects, click Forms, and then click New.
- When the New Form dialog box appears, click Design View, and then click OK.
The new form appears.
To create the Beverages combo box
- In the Toolbox, click Combo box.
Note If the toolbox is not visible, on the View menu, click Toolbox.
- Make sure that the Control Wizards button in the Toolbox is selected. Then click and drag the combo box to the location on the form where you want it to appear.
- When the Combo Box Wizard appears, click I want the combo box to look up the values in a table or query. Click Next.
- Click the Categories table, and then click Next.
- In the Available Fields box, click CategoryName. Click the right arrow to move the CategoryName field to the Selected Fields box, and then click Next.
- In the first combo box, for the sort order, select CategoryName, and then click Next.
- Leave the Hide Key Column (recommended) check box selected, and then click Next.
- Change the label for the combo box to Category Name:, and then click Finish.
- Right-click the new combo box and click Properties.
- Set the values of the following combo box properties as shown:
|| Set value to:
To create the Category Name, Enter start date, and Enter end date text boxes
- In the Toolbox, click Text box, and then drag the new text box to the position where you want it on the form.
A new text box appears, with a label to its left.
- Right-click the label for the new text box, and click Properties. When the Properties window appears, change the Caption property of the label to Enter start date:.
- With the Properties window still open, click the text box. Change the Name property of the text box to StartDate.
- Change the Default Value property to of the text box to ="1/1/1997", and close the Properties window.
- Add another text box by clicking Text box in the Toolbox. Drag the new text box to the position where you want it on the form.
A label appears to the left of the new text box.
- Right-click the label, and choose Properties. When the Properties window appears, change the Caption property of the label to Enter end date:.
- With the Properties window still open, click the text box and change its Name property to EndDate.
- Change the Default Value property of the text box to ="12/31/1997".
- Close the Properties window.
- Right-click the Category Name label, point to Size, and click To Fit. Then, repeat for the Enter start date and Enter end date labels.
To set form properties
- Reduce the size of the form by dragging the lower-right corner of the form inward.
- On the Edit menu, click Select Form.
- On the View menu, click Properties.
- When the Properties window appears, click the click the Format tab, and set the Border Style property to Dialog.
- Set the values of the following form properties as shown:
|| Set value to:
||Sales By Category Summary Query
|Allow Datasheet View
|Allow PivotTable View
|Allow PivotChart View
|Min Max Buttons
To save the form
- On the File menu, click Save As.
- In the Save As dialog box, type Sales By Category Form.
- Click OK.
After you have the form and its controls working as planned, save the form and turn your attention to modifying the companion query. First, open the query in Design view. Then, where you otherwise would have used bracketed parameters, change them so that instead they reference items on your form (for detailed instructions, see the Step by Step section). For example, to have Access use the value in the Category control on your form as the parameter for the Category field in your query, you would enter the following text in the Criteria row of the query design grid:
[Forms]![Sales By Category Form]![Category]
The first component, [Forms], tells Access to look in the Forms collection (collection: An object that contains a set of related objects. An object's position in the collection can change whenever a change occurs in the collection; therefore, the position of any specific object in the collection may vary.). Access uses many collections — there are also collections for tables, queries, reports, data access pages, macros and modules. Typically, Access organizes objects into collections.
The second component identifies the specific form in the Forms collection. In this case, it's [Sales By Category Form]. Finally, the third component, [Category], identifies the name of the control. A name containing all three components (as does our example) is often said to be fully qualifed. There are times when you can include just the name of the control (not a fully qualified name) and Access will automatically determine the rest.
This isn't one of those times, because we need to point Access to a specific form.
So, reading in reverse order, this criteria entry instructs Access to obtain the value of the Category control in the Sales By Category Form form in the Forms collection of the currently open database. This value becomes the criteria for that cell in the query design, as if you entered it directly.
You would take the same approach to entering the criteria for the Order Date field, substituting the name of the appropriate control from the form where Access should look to obtain the parameter. In the end, your query design should look something like this:
For this example, I took the Sales By Category query from the Northwind.mdb sample database that ships with Access, and then modified and saved it as "Sales By Category Query."
Here are the step-by-step instructions:
Step by Step: Modify your query so that it obtains its parameters from the controls on the form.
- Open the query in Design view. For this example, I'll use the Sales By Category query from Northwind.mdb.
- In the query design grid, in the CategoryName column, click the Criteria field.
- Type [Forms]![Sales By Category Form]![Category]
- In the query design grid, in the OrderDate column, click the Criteria field.
- Change the criteria to Between [Forms]![Sales By Category Form]![StartDate] And [Forms]![Sales By Category Form]![EndDate]
- On the File menu, click Save As. In the Save As dialog box, type Sales By Category Query, and then click OK.
Adding command buttons to the parameters form
To provide a way to submit the parameters or cancel the query, you will need to add two command buttons. Typically, these two buttons are OK and Cancel. You click OK when you've completed entering your parameters and are ready to submit the query. You click Cancel when you've changed your mind and no longer wish to run the query.
Adding the OK and Cancel command buttons is a simple process. To add a command button, open the form in Design view, select the Toolbox, and click Command Button in the Toolbox. If the Toolbox isn't visible, on the View menu, click Toolbox. Next, in the Toolbox, click Command Button, drag the mouse pointer onto the form, and click once to create the command button. If the Command Button Wizard appears, click Cancel.
Do this once to create the OK button and then again to create the Cancel button. At this point, the two buttons will have generic names like "Command1" and "Command2" (or "Command" and some other number).
Next, right-click the first button and click Properties. When the Properties window appears, change the caption to OK. Then click the Event tab. When the Event tab appears, click the On Click event (event: An action recognized by an object, such as a mouse click or key press, for which you can define a response. An event can be caused by a user action or a Visual Basic statement, or it can be triggered by the system.) field.
The On Click event occurs whenever you click a command button in Form view. By attaching some VBA code to this event, you can tell Access what to do when you or your user clicks the command button. Click the ellipsis button (...) when it appears next to the field.
The Choose Builder dialog box then appears. Click Code Builder, and then click OK. Enter the following code in the code editor so that the OK_Click procedure appears like this when you're done:
Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "Sales By Category Query", acViewNormal, acEdit
DoCmd.Close acForm, "Sales By Category Form"
This procedure first makes the parameters form invisible. Next, the DoCmd.OpenQuery statement runs the query named Sales By Category Query. This is the query whose parameters reference the values in controls on this form. Having invoked the query, the procedure then closes the parameters form.
The Cancel button also requires a small amount of VBA code attached to its On Click event. First, right-click the second button and click Properties. When the Properties window appears, change the Caption property to Cancel. Next, click the Event tab. When the Event tab appears, click the On Click event, and then click the ellipsis button (...). When the Choose Builder dialog box appears, select Code Builder, and then click OK. Enter the following code in the code editor so that the Cancel_Click procedure appears like this when you're done:
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
When you're finished, on the File menu, click Save. Then, on the File menu, click Close and Return to Microsoft Access. Finally, close all windows except the Database window. That's it! Now you can run the form Sales By Category Form, which will in turn run the parameter query.
The form Sales By Category Form and query Sales By Category Query that demonstrate the above technique are included in this article's sample database.
Using parameters with reports
A parameter query is a great way to ask a question and display the answer, particularly when the question is one where you often change the criteria. Sometimes, however, you want the answer presented in a way that permits more control over how data is displayed, more flexibility in organizing and presenting summary information, and more support specifically for printing information, especially information that spans more than one page. For that, you need a report.
The Report Designer feature of Access offers a lot of flexibility in how you can present your information. That flexibility also extends to selecting the source of the data used in the report. For example, you can base a report on either a table, a query, or a Structured Query Language (SQL) statement. SQL is the language used most often for querying, updating, and managing databases. All Access queries are expressed behind the scenes in SQL.
Because you can use a query as the basis for a report, you can also use a parameter query. To do so, you set the report's RecordSource property to the name of the parameter query. First, open the report in Design view, and then, on the Edit menu, click Report. Finally, on the View menu, click Properties.
You can then click the Record Source combo box and choose a query or table. The RecordSource property tells Access the source of the data for the report. When you run the report, Access runs the query and prompts you for the parameters in the same way it does when you run the query by itself.
Each use of a parameter results in another Enter Parameter Value dialog box when Access runs the report, so if you have more than one parameter, you'll want to consider using a form here, too. The report called Sales By Category Report, in the sample database, demonstrates one approach to using a form to submit parameters to a report. This approach relies in part on the order in which Access processes events.
Creating a form to supply parameters to a report
Access uses a model where events occur and are generated by both the system and the user. For example, clicking the mouse generates an event. Opening a form in Access generates a number of events. Even running a report generates a series of events. Many of these events can be responded to by VBA code. Indeed, responding to events is the primary means by which you customize an Access database. Therefore, knowing which event to respond to is of key importance.
When Access opens a report, it first runs the code attached to the report's Open event. Because Access processes the Open event before it runs the query that provides the report with its data, you can run a form from the Open event that supplies the query with parameters. This provides the basis for the technique employed by the Sales by Category Report report in the sample database.
Here's how it works:
- You open the report.
- Access processes the code attached to the report's Open event.
- That code opens a form that serves to collect the parameters.
- You enter the parameters and click OK.
- Access hides the form, but the form does not close.
- Access then processes the report's underlying query.
- That query retrieves parameters from the controls on the form that is currently hidden, does its work, and then delivers its results to the report.
- The report finishes, and displays its results.
- When you close the report, code in the report's Close event also closes the hidden form.
To demonstrate this technique, I created a form (Sales by Category Dialog) and a query (Source Query for Sales By Category Report), and I then modified a copy of the Sales by Category Report report from the Northwind.mdb sample database that ships with Access. I modified the report so that when you open it for printing or previewing, it opens a form to collect the parameters. Access then runs the query to provide the report with its data — that query uses values from the form to supply the report with its parameters. The form, query, and report can all be found in the sample database.
To enter code in the report's Open and Close events, you start by opening the report in Design view. Then, on the Edit menu, click Report. Next, on the View menu, click Properties. Click the Event tab, and click the OnOpen property. Click the ellipsis button (...) when it appears next to the field. When the Choose Builder dialog box appears, click Code Builder, and then click OK.
The VBA code in the Close and Open events of the report named Sales by Category Report looks like this:
Private Sub Report_Close()
DoCmd.Close acForm, "Sales By Category Dialog"
Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True
' Open Sales By Category Dialog
DoCmd.OpenForm "Sales By Category Dialog", , , , , acDialog
' Cancel Report if User Clicked the Cancel Button
If IsLoaded("Sales By Category Dialog") = False Then Cancel = True
' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
The report's Open event procedure (Report_Open) works by first setting the value of a variable (variable: A named storage location capable of containing data that can be modified during program execution. Each variable has a name that uniquely identifies it within its level of scope. A data type can be specified or not.), bInReportOpenEvent, to True, to indicate that the report's Open event is in progress. This is done primarily so that other procedures, in particular the Open event procedure on the Sales By Category Dialog form, can reference the variable and determine whether the report's Open event is in progress.
Next, the DoCmd.OpenForm statement opens the form so that you can enter your parameter values. When you submit the form, either by clicking OK or Cancel, the code checks to see if the form was closed (that is, it makes sure that you clicked Cancel). Recall that when you click OK, the form is hidden so that the parameters can remain available to the report's underlying query. If the form is closed, the report will be canceled. Finally, the bInReportOpenEvent variable is set to False to indicate that the report's Open event is completed.
The report's Close event has the relatively simple task of closing the hidden form when the report closes.
Placing code in the form's events
The steps used to create the Sales By Category Dialog form are the same as those used earlier to create a form to supply parameters to the query. The difference is not in how you create the form, but in what code you place in the form's events. For example, because this form is supposed to be called directly from the report's Open event (instead of on its own or from another procedure), the form's Open event includes code to check for that. The Sales By Category Dialog form's code looks like this:
Private Sub Cancel_Click()
Private Sub Form_Open(Cancel As Integer)
If Not bInReportOpenEvent Then
' If we're not called from the report
MsgBox "For use from the Sales By Category Report only", _
Cancel = True
Private Sub OK_Click()
Me.Visible = False
When the form opens, Access calls the form's Open event procedure (Form_Open). That code checks to see if the report's Open event is in progress. If not, it follows that the form must have been opened from outside of the report. For instance, it could have been opened directly from the Database window. Because the form is not intended to be used in this way, the code displays an appropriate message, and the form is closed. If, on the other hand, the report's Open event is indeed in progress, all is well, and thus the form's Open event procedure will exit gracefully and the form will be available for editing. You can then enter and edit your parameters.
When you click OK, the form's Visible property is set to False. This hides the form so that values entered into the form's controls can remain available, and the report can continue processing. If you click Cancel instead, it means you've decided not to run the report, and thus the form is closed. The report's Open event code detects this, and cancels the report.
Adding a supporting code module
In support of the code above, you need to create a module and add the code shown below. To create a new module, click Modules in the Database window under Objects, and then click New. When the editing window appears, type the code so that it appears exactly as shown below:
Option Compare Database
Public bInReportOpenEvent As Boolean ' Is report in the Open event?
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or
' Datasheet view.
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
When you're finished, click the Save button on the toolbar and type a name for the module when prompted. Then, on the File menu, click Close and Return to Microsoft Office Access.
Download the sample database
The sample database includes the forms, queries, and reports that demonstrate the use of parameters with sample data drawn from the Northwind.mdb sample database that is included with Access.
For more information
- For more information about parameter queries, see these Microsoft Knowledge Base articles:
- For introductory online training on parameter queries, see Office Online's own training course, Queries III: Create parameter queries that prompt users for input.
- For an excellent, in-depth treatment of many topics concerning queries and reports, see Getz, Ken. Litwin, Paul. Gunderloy, Mike. Access 2002 Desktop Developer's Handbook, SYBEX, Inc., 2001.
- For a solid foundation on Microsoft Access, including discussion of queries and reports and an introduction to parameter queries, see Viescas, John L. Microsoft Office Access 2003 Inside Out, Microsoft Press, 2003.