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.
Microsoft Access is a fully capable database and it's primary limitations is the number of concurrent users it will support. Generally, we recommend a limit of 10 to 15 users.
We create databases large and small. Some of our databases help run entire small businesses. We also handle unique businesses such as flyrod manufacturing, cheese making, marina management, and cable tv inventory.
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
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
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
Public Function get_global(G_name as string)
' determine query criteria values
Select Case G_name
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
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
More References for Using Access Query Criteria Parameters
Global Variables as Parameters
Visual Basic Global Variables in Query Criteria
Access Query Examples SQL