Parameter Queries in Microsoft Access

Query Criteria Examples

Access Parameter Query 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 criteria query from a form called F_Emp_Report. Follow our SQL parameter criteria 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 a Microsoft Access error saying that the form or field Forms![F_Emp_Report]!Start_Date (the criteria field) cannot be found then you need to get the latest update for Access.  Referencing a form when using an aggregate function (like count, max, min, and avg) produces this error when using form fields for query parameters.

Send Values to Access SQL Queries:

Use global variables as query criteria 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’);

 

Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016