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.
Are you just starting out on your quest for a database? Need some advice or someone to bounce ideas off of? We are here and will be happy to chat with you on the phone and discuss options to your situation. Visit our contact page and give us a call.
Did you know that most template databases often do not do exactly what you need done? We can create a custom template that fits your needs exactly for a small to medium Access database at a reduced cost. See our contact page and call Joseph Dean for more info.
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