Share this page:

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

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

Microsoft Access

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