---

MS Access 2003: Filter report results using the OpenReport VBA command

This MSAccess tutorial explains how to filter report results using the OpenReport VBA command in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft 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