Access VBA change Query criteria using QueryDef.SQL 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.

 

Access Report Section Property

This Access tutorial explains how to use Access Report Section Property to control Report Section such as Page Header, Footer.

Access Report Section Property

In Access Report VBA, you can directly access the Controls in the Report, it is also possible to access the Report Sections using Report Section Property, such as Page Header, Group Header, Page Footer, etc. This is especially useful to hide the whole Section.

Syntax of Report Section Property

Me.Section(Index)
IndexConstantDescription
0acDetailReport detail section
1acHeaderReport header section
2acFooterReport footer section
3acPageHeaderReport page header section
4acPageFooterReport page footer section
5acGroupLevel1HeaderGroup-level 1 header section
6acGroupLevel1FooterGroup-level 1 footer section
7acGroupLevel2HeaderGroup-level 2 header section
8acGroupLevel2FooterGroup-level 2 footer section

Example of Access Report Section Property

To hide Group Header Section in an Event

Me.Section(5).Visible = False

You may also read my another example below

Access Report page break by Group and add blank page

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff192668.aspx