Multiple Parameters For Query ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Queries are an essential element in data processing and we use them a lot in various ways. While creating Queries the main issue we are faced with is how to filter data in a user-friendly manner and make things work effortlessly for the User. We use several methods to facilitate the user to pass values as criteria to the Queries.

Creating Parameter Queries and inserting Variables (like [Enter Sales Date]) in Query's Criteria row, so that the Query will prompt for Parameter Values and the User can Key-in them directly and filter records. The Data Type for the Parameter Variable can be defined by selecting the Parameters. . . Option from the Query Menu when you are in the Design view of the Query.
Creates TextBoxes or Combo Boxes on the Form and the User fills in values into them and runs the Report or Data views. The underlying Queries will have a reference to the TextBoxes or Combo Boxes on the Form in the Criteria Row, like Forms![MyForm]![myDateCombo], and based on the values in them, the data filtering takes place for Reports or Views.
Another way of selecting records is based on a range of Values. For example, filtering Sales records for a certain period and the criteria set in the Query for the Sales Date will be something like Between #01/01/2008# AND #03/31/2008# if the values are used in Constant form. But, these values also can be passed from TextBoxes from a Form too.
What I prefer to do in these cases, creates a small table (let us call it, a Parameter Table) with one record and two fields for StartDate and EndDate and create a Datasheet Form and place it as a Sub-Form on the Main Form so that theUser can conveniently key in the date range values into the Table.

This table will be included in the main Query and the StartDate, and EndDate fields are placed in the Criteria row with the expression Between [StartDate] AND [EndDate]. It is important to see that this Parameter table has only one record in it otherwise the records selected from the main table will be doubled if the parameter table has two records. We can control this by setting the Allow Additions Property Value, of the Data Sheet Form, to No so that the user is prevented from adding more records by mistake.
When the user clicks a button in the Report or for other outputs based on this date ranges we can run the Query after refreshing the Parameter Sub-Form to update the changed value in the table.
The above example, asks for all the data between StartDate and Update. But there are times that we need data, the intermittent values like Employee Codes 1, 5, 7, 8, and we are forced to input the Code in the criteria row in one of three ways like the sample image given below:

Query Parameter Input Methods

I would like to present here another method that I use to provide the Users to select Parameter Values for Reports by putting check marks in the Parameter Table.

Assume that our Company has Branch Offices across the Country and the Management may ask for Reports on selected Branches. Since Branch Names are constant values all we need to do is to select the required Branches by putting check marks on their side and the selected cases can be used as criteria for filtering Data.

To have a closer look at this method and for simplicity, we will use the List of Months for our example and see how the selected Months are used in the Criteria of the Main Query. The Image of the List of Months presented to the user in a Datasheet Form (as a Sub-Form on the Main Form) is given below:

We need two Queries, one to filter the selected months from the list and the second the Main Query in which we will use the Values from the first Query as a Parameter to filter Data for the Report. Our first Query must come out with the result values 3,6,9 & 12 as per the Month selection shown on the image above. The following SQL string is used for this purpose:

Query Name: Month_ParamQ

SELECT Month_Parameter.MTH
FROM Month_Parameter
WHERE (((Month_Parameter.[SELECT])=True));

When the User puts check marks on the Parameter screen the selection may not immediately update in the underlying Month_Parameter Table. To update the change we have to Refresh the Month_Parameter Sub-Form before opening the Report that pulls data from the Main Query that uses the above Query as criteria. For that, we have written a statement on the On_Click() Event Procedure of the Print Preview Command Button as below.

Private Sub cmdPreview_Click()
     Me.Month_Parameter.Form.Refresh
     DoCmd.OpenReport "myNewReport", acViewPreview
End Sub

Now, how the selected months filtered in the Month_ParamQ can be used in the Main Query as criteria? It is easy, to look at the third method we have used as criteria in the first Image given above. I will repeat it here below:

IN(1,5,7,8)

Here, we will compare the EmployeeID values, with the number 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.