Parameter Query in Microsoft Access

Access Query Criteria Methods

Query parameters example is an extension of the bottom-up query example.  The form field is used in the query to determine how far to look back in the M_Attendance table for missed work days.  Also, you are running this parameter query from a form called F_Emp_Report.

Access Query Parameters Code Example:

Select M_Employees.Name, M_Employees.Emp_Number
From M_Employees
Where M_Employees.Employee_ID in
(Select Employee_ID from M_Attendance
Where M_Attendance.Attendance_Date >= Forms!F_Emp_Report!Start_Date
Group By Employee_ID
Having Count(M_Attendance.Day_Missed) >= 5);

Note:  If you get an Microsoft Access error saying that the form or field Forms![F_Emp_Report]!Start_Date (the parameter) cannot be found then you need to get the latest update for Access 2000, Version 9.0.4402 SR-1.  Referencing a form when using an aggregate function (like count, max, min, avg) produces this error when using form fields for query parameters.

More Advanced Query Criteria Methods:

Use global variables as query parameters. The setup is as follows:  Make a Module with the following code:

Option Compare Database

Global GBL_Project_ID As Long
Global GBL_Start_Date As Date
Global GBL_End_Date As Date

Option Explicit

Public Function get_global(G_name as string)
    ' determine query criteria values
     Select Case G_name
            Case "Project_ID"
                    Get_Global=GBL_Project_ID
            Case "Start_Date"
                    Get_Global=GBL_Start_Date
            Case "End_Date"
                    Get_Global=GBL_End_Date
    End Select
End Sub

In the form that causes the query (or report) to run, set the global variable in an on-click event of a button.  In the example below the user has select a project from a combo box so we save the project_id in our global variable.

GBL_Project_ID=Project_Combo

In our query, we use the Get_Global function to pass the selected project_ID criteria to the query:

Select Project_ID, Project_Name from M_Projects
Where Project_ID=Get_Global('Project_ID');

More References for Using Access Query Parameters