This Access VBA tutorial explains how to change Query criteria in VBA using QueryDef.SQL Property.
You may also want to read:
Create Query using CreateQueryDef
Access VBA – change Query criteria
It is easy to change Query criteria in Query View, but sometimes your criteria may depend on a variable that is not a constant. In Access VBA, you can change the Query criteria using QueryDef.SQL Property. In fact, QueryDef.SQL does not really update just the update the criteria of the statement, it overwrites the SQL statement of a Query. The reason is that VBA cannot split the SQL statement into SELECT TABLE part and criteria part, and then only update the criteria statement.
VBA Code – change Query criteria
In the below example, I change the SQL statement of tempQry to the SQL highlighted in blue.
Private Sub updateQuery()
Dim db As dao.Database
Set db = CurrentDb
Dim qdf As dao.QueryDef
Set qdf = db.QueryDefs("tempQry")
qdf.SQL = "Select * From [employee_tbl] WHERE [Department]='HR'"
End Sub
Example – change Query criteria
Let’s say we have a Query called tempQry, which select all employee data whose Department is Finance. Our goal is to change the criteria to HR department.
Close the Query Design. Press ALT+F11 and then copy the VBA code (in the above example) to a new Module, and then run the Macro.
Open tempQry in Design View again. Now you can see the criteria has changed to HR.
