Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Multiple Parameters For Query

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.

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

  2. 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.

  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 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.

  4. 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.

Share:

1 comment:

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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