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 effortless for the User. We use several methods to facilitate the user to pass values as criteria to the Queries.

1. 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 Design view of the Query.

    

2. Creates Text Boxes 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 Text Boxes 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.

    

3. Another way of selecting records is based on a range of Values. For example filtering Sales records for a certain period and the criteria setting on 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 Text Boxes from a Form too.

    

   What I prefer to do in these cases, create a small table (let us call it as 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 the User can conveniently key in the date range values into the Table.

    

   This table will be included in the main Query and the StartDate, 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 for the Report or for other outputs based on this date-range we can run the Query after refreshing the Parameter Sub-Form to update the changed value in the table.

    

4. The above example asks for all the data between StartDate and Endate. But there are times that we need data of 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 example image given below:

    

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 by 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 Sub-Form on a Main Form) is given below:

We need two Queries, one to filter the selected months from the list and second the Main Query in which we will use the Values from the first Query as 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 it:

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, 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 are asking to compare the EmployeeID values with the numbers 1,5,7,8 and select records that match with 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 statement when written in the form of a sub-query it will look like the following:

IN(SELECT MTH FROM MONTH_PARAMQ)

The User don’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.

Opening Excel Database Directly

Display Excel Value Directly on Form

Opening dBase Files Directly

Opening External Data Sources