Access: Filter report results using the OpenReport VBA command in Access 2003/XP/2000/97

Question: In Access 2003/XP/2000/97, I have a report and I would like to be able to filter the report results without hardcoding parameters in the Query Builder. How can I do this?

Answer: You can use the OpenReport command in VBA to open a report and filter the results in many different ways.

We've created an Access sample that you can download.

Download Access 2000 version

In this example, we've created 3 buttons:

All Suppliers button
Current Supplier only button
SupplierID > 5 and CompanyName starts with S button

Each of these buttons will apply a different filter to the rptSuppliers report allowing you to filter your report results in whatever way you see fit.

The VBA code behind the "All Suppliers" button is:

Private Sub cmdAll_Click()

'Display all suppliers in rptSuppliers report
DoCmd.OpenReport "rptSuppliers", acViewPreview

End Sub

The VBA code behind the "Current Supplier only" button is:

Private Sub cmdCurrent_Click()

    'Filter report to display only Supplier currently showing on frmExample
    ' (by SupplierID field)
    DoCmd.OpenReport "rptSuppliers", acViewPreview, , "SupplierID = " & SupplierID

End Sub

The VBA code behind the "SupplierID > 5 and CompanyName starts with S" button is:

Private Sub cmdComplex_Click()

    'Filter report to display only Suppliers whose SupplierID > 5 and
    ' CompanyName starts with S
    DoCmd.OpenReport "rptSuppliers", acViewPreview, , "SupplierID > 5 and CompanyName like 'S*'"

End Sub