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