Save Time Running Microsoft Access Reports using Filters in Layout View

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)

VIEW MORE COURSES >

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.

How to Combine First and Last Name in Excel (5 Ways)

You can combine first and last name in Excel in several ways – using the CONCATENATE operator, the CONCATENATE function, the CONCAT function, the TEXTJOIN function or Flash Fill. These functions are often used to combine text in cells but you can also combine text with spaces, commas, dashes or another character. It's common to combine first and last names that appear in two columns into one new column. Some functions are only available in newer versions of Excel but the CONCATENATE operator and function are available in all versions.

How to Insert Multiple Columns in Excel (4 Fast Ways with Shortcuts)

You can quickly insert multiple columns in Excel using a drop-down menu, keyboard shortcuts or the Home tab in the Ribbon. The key to inserting multiple columns at once, rather than one by one, is to select the number of columns you want to insert first and then insert columns. Excel will insert the same number of columns you selected.

Excel XLOOKUP Function (Overview, Syntax + Formula Examples)

The XLOOKUP function is a replacement for Excel's traditional VLOOKUP function (as well as HLOOKUP and INDEX / MATCH functions). It has a new set of arguments and is available in Excel 2021 and 365. It allows you to look up a value from an array in a range or table and return one or more results. One of the primary benefits of XLOOKUP is that it can look up from columns to the left in a data set and return a range.

Save Time Running Microsoft Access Reports using Filters in Layout View