Unit 5 – Access VBA Object Property

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.