How to Quickly Filter Microsoft Access Report Data in Layout View
by Avantix Learning Team | Updated April 9, 2021
Applies to: Microsoft® Access® 2010, 2013, 2016, 2019, 2021 and 365 (Windows)
In newer versions of Microsoft Access, you can use Layout View to quickly filter data in a report without having to create a new query to use as the source for the report. This can save you a lot of time.
You can also apply filters in Report View (which is a view that doesn't offer the same formatting functionality as Layout View). In this article, we're going to use Layout View.
You can filter reports created in desktop databases with Microsoft Access using this method but not in a Microsoft Access app.
Recommended article: 10 Timesaving Shortcuts in Microsoft Access
Do you want to learn more about Microsoft Access? Check out our virtual classroom or in-person classroom Access courses >
Filter text fields in Layout View
To filter a text field in a report:
- View the report in Layout View. If you are in Design View, right-click the tab or title bar of the report and choose Layout View. If you are opening a report, right-click on the report in the Navigation Pane and choose Layout View.
- Right-click the data you want to filter. For example, if you would like to filter a City field, right-click on any city and a context sensitive menu will appear. The options in the menu will appear based on the type of field.
- From the menu, select the desired options. You may select a custom option. For example, if you select Text Filters, other options appear such as Begins with.
- Select the desired option. The report will appear in its filtered state.
Below is the menu as it appears if a user right-clicks on a city in a City field:
Filter number fields in Layout View
To filter a number field in a report:
- View the report in Report or Layout View. If you are in Design View, right-click the tab or title bar of the report and choose Layout View. If you are opening a report, right-click on the report in the Navigation Pane and choose Layout View.
- Right-click the data you want to filter. For example, if you would like to filter an Actual Sales field, right-click on any actual sales data and a context sensitive menu will appear. The options in the menu will appear for number fields.
- From the menu, select the desired options. You may select a custom option. For example, if you select Number Filters, other options appear such as Between.
- Select the desired option. The report will appear in its filtered state.
Below is the menu as it appears if a user right-clicks on data in an Actual Sales field:
Filter date fields in Layout View
To filter a date field in a report:
- View the report in Layout View. If you are in Design View, right-click the tab or title bar of the report and choose Layout View. If you are opening a report, right-click on the report in the Navigation Pane and choose Layout View.
- Right-click the data you want to filter. For example, if you would like to filter a Date Entered field, right-click on a date entered and a context sensitive menu will appear. The options in the menu will appear for date fields.
- From the menu, select the desired options. You may select a custom option. For example, if you select Date Filters, other options appear such as Between.
- Select the desired option. The report will appear in its filtered state.
Below is the menu as it appears if a user right-clicks on data in a Date Entered field:
Clear or remove filters
To clear filters:
- In Layout View, click the Home tab in the Ribbon.
- In the Sort and Filter group, click Advanced and select Clear All Filters from the drop-down menu. Access clears or removes all of the filters. You can also right-click the field and select Clear.
Toggle filters
You can also toggle filters off and on:
- In Layout View, click the Home tab in the Ribbon.
- Click the Toggle Filter button to remove the filter or click on Toggle Filter to reapply the filter.
- If you close the report without clearing the filters, the filters will be available when you reopen the report and you can click on Toggle Filter again to reapply them. The filters will be available even if you close and reopen the database.
This method of filtering in reports is both simple and intuitive and should result in fewer queries.
This article was first published on June 6, 2016 and has been updated for clarity and content.
Subscribe to get more articles like this one
Did you find this article helpful? If you would like to receive new articles, JOIN our email list.
More resources
10 Tips for Creating Select Queries in Microsoft Access
How to Convert a Microsoft Access Report to PDF (3 Ways)
How to Create a Calculated Field in a Microsoft Access Query
10 Tips for Working with Controls on Forms and Report in Microsoft Access
How to Highlight Records or Values in a Microsoft Access Report Using Conditional Formatting
Related courses
Microsoft Access: Introduction
Microsoft Access: Intermediate / Advanced
Microsoft Access: Introduction to VBA (Visual Basic for Applications)
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2024 Avantix® Learning
You may also like
How to Replace Zeros (0) with Blanks in Excel
There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.
What is Power Query in Excel?
Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.
How to Freeze Rows in Excel (One or Multiple Rows)
You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca