Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Multiple Parameters For Query

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()
     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:


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:


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.



  1. Pretty nice post. I just stumbled upon your blog and wanted to say that I have really enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!



Your email address:

Delivered by FeedBurner


Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Class Module Combo Boxes Custom Wizards DOS Commands Data Data Type Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation msaccessprocess security advanced Access Security Custom Functions Field Type Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Fields Form Form Instances Join Methods Microsoft Numbering System Records Recordset Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Access Class Module and Wrapper Classes

There are times that a particular Class Module needs to be instantiated more than once, to use similar set of values for a particular Applic...


Blog Archive

Recent Posts