Unable to filter reports in Microsoft Access database

Hello Allan Plucinik

Thank you for reaching out to Microsoft Q&A and for providing a detailed description of the issue you're experiencing in Access. Similar issues have been reported, where the Advanced Filter/Sort feature for reports (not based on queries) incorrectly switches to the Query Design ribbon instead of staying in the Home ribbon context. This makes Toggle Filter unavailable or greyed out and treats the filter as a standalone query rather than applying it directly to the report. here are some workarounds to apply date range filters to your reports without relying on the broken Advanced Filter/Sort:

Switch to a Query-Based Report

• Create a new query based on your report's underlying table(s).
• In the query design grid, add your criteria, e.g., for JobDate: Between #9/1/25# And #9/30/25#.
• Save the query (as "FilteredJobsQuery").
• Open your report in Design view, go to the Property Sheet > Data tab, and set the Record Source to your new query.
• Save and run the report—it will now only include the filtered records in your custom format.
• For dynamic ranges, use parameters like Between [Start Date] And [End Date] in the criteria. When you run the report, Access will prompt for the dates.

This avoids the ribbon glitch entirely since the filter is baked into the query.

Use VBA to Open the Report with a Filter

If you need to apply filters on-the-fly without changing the report's design:

• Create a button on a form or use a macro/VBA module.
• Use code like this to open the report with a WhereCondition (adjust field names/dates as needed):

    DoCmd.OpenReport "YourReportName", acViewReport, , "JobDate Between #9/1/25# And #9/30/25#"
  

  1. This applies the filter at runtime without needing Advanced Filter/Sort.
  2. For user-input dates, prompt with InputBox or a form:

          Dim StartDate As Date, EndDate As Date
          StartDate = InputBox("Enter Start Date (mm/dd/yy):")
          EndDate = InputBox("Enter End Date (mm/dd/yy):")
          DoCmd.OpenReport "YourReportName", acViewReport, , "JobDate Between #" & Format(StartDate, "mm/dd/yy") & "# And #" & Format(EndDate, "mm/dd/yy") & "#"
     

Hope this helps.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.