Introduction.
Queries are an essential component of data processing, and we rely on them extensively in various ways. One of the main challenges when creating queries is how to filter data in a user-friendly manner, making the process seamless for the user. To address this, we employ several methods that allow users to easily pass values as criteria to the queries.
You can create Parameter Queries by inserting variables, such as,
[EnterSalesDate],Into the Criteria row of a query. When run, the query will prompt the user to enter the parameter value, allowing them to filter records directly. To define the data type for a parameter variable, use the Parameters… option from the Query menu while in Design View.You can place TextBoxes or Combo Boxes on a Form, where the user can enter or select values before running a Report or viewing data. The underlying queries reference these controls in their Criteria rows—for example,
Forms![MyForm]![myDateCombo]. Based on the values entered or selected, the queries filter the data accordingly, producing the desired results in Reports or data views.Another way to filter records is by specifying a range of values. For example, to retrieve Sales records for a particular period, the query criteria for the Sales Date might be
Between #01/01/2008# AND #03/31/2008#if constants are used. Alternatively, these values can be dynamically passed from TextBoxes on a Form, allowing the user to specify the date range interactively.In such cases, I prefer to create a small table—let’s call it a Parameter Table—with a single record and two fields:
StartDateandEndDate. Then, I create a Datasheet Form for this table and embed it as a Sub-Form on the Main Form. This allows the user to conveniently enter the date range values directly into the table.This table is included in the main query, with the
StartDateandEndDatefields placed in the Criteria row using the expression:It is important to note that the Parameter Table should contain only one record; otherwise, the main table’s results will be duplicated if the Parameter Table has multiple records. To prevent this, set the Allow Additions property of the Datasheet Form to No, so the user cannot inadvertently add more records.
When the user clicks a button to generate the Report or other outputs based on this date range, the Parameter Sub-Form can be refreshed first to update the values in the table. After that, the query can be executed to reflect the latest StartDate and EndDate values.
- The above example retrieves all data between StartDate and EndDate. However, sometimes we need to filter specific, non-sequential values—for instance, Employee Codes 1, 5, 7, and 8. In such cases, we are forced to enter the codes manually in the Criteria row of the query, using one of several methods, as illustrated in the sample image below:
Query Parameter Input Methods
I would like to share another method I use to let users select parameter values for reports—by simply checking boxes in a Parameter Table.
For example, assume that our company has several branch offices across the country, and management occasionally requests reports for selected branches. Since branch names remain constant, we can enable users to pick the required branches by placing check marks beside them. The check-marked entries can then serve as criteria for filtering data.
To illustrate this method more clearly (and to keep it simple), let’s use a list of months as an example. We will see how the selected months are used in the criteria of the main query. The image below shows how this list of months appears to the user in a datasheet form, displayed as a subform on the main form.
We will need two queries for this process—one to filter the selected months from the list, and a second (the main query) that uses the results of the first query as parameters to filter data for the report.
The first query should return the values 3, 6, 9, and 12, based on the month selections shown in the image above. The following SQL statement can be used to achieve this result:
Query Name: Month_ParamQ
SELECT Month_Parameter.MTH FROM Month_Parameter WHERE (((Month_Parameter.[SELECT])=True));
When the user selects or deselects check marks on the parameter screen, these changes may not immediately update in the underlying Month_Parameter table. To ensure the latest selections are reflected, we must refresh the Month_Parameter subform before opening the report that retrieves data from the main query (which uses the above query as its criteria).
To handle this, include the following statement in the On_Click() event procedure of the Print Preview command button:
Private Sub cmdPreview_Click()
Me.Month_Parameter.Form.Refresh
DoCmd.OpenReport "myNewReport", acViewPreview
End Sub
Now, how can the selected months filtered in the Month_ParamQ be used in the Main Query as a criterion? It is easy to look at the third method we have used as a criterion in the first Image given above. I will repeat it below:
IN(1,5,7,8)
Here, we will compare the EmployeeID values with the numbers 1, 5, 7, 8, and select records that match any of these numbers as output.
Similarly, all we need to do here in the Main Query is to write this as a Sub_Query in the Criteria Row to use the Month Values from the Month_ParamQ. The above criteria clause, when written in the form of a sub-query, will look like the following:
IN(SELECT MTH FROM MONTH_PARAMQ)
The User doesn't have to type the Parameter values for the Report, only put check marks on the required items, click a Button, and the Report is ready.
Download Demo Database Access2007.zip
