How to Quickly Filter Microsoft Access Report Data in Layout View

by Avantix Learning Team | Updated October 21, 2019

Applies to: Microsoft® Access® 2010, 2013, 2016, 2019 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: 10 Timesaving Shortcuts in Microsoft Access

Filter text fields in Layout View

To filter a text field in a report:

  1. 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.
  2. 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.
  3. 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.
  4. 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:

 Filtering text fields in a Microsoft Access report in training.

Filter number fields in Layout View

To filter a number field in a report:

  1. 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.
  2. 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.
  3. 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.
  4. 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:

Filtering number fields in a Microsoft Access report in training.

Filter date fields in Layout View

To filter a date field in a report:

  1. 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.
  2. 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.
  3. 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.
  4. 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:

Filtering date fields in a Microsoft Access report in training.

Clear or remove filters

To clear filters:

  1. In Layout View, click the Home tab in the Ribbon.
  2. 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.

Clearning all filters in a Microsoft Access report.

Toggle filters

You can also toggle filters off and on:

  1. In Layout View, click the Home tab in the Ribbon.
  2. Click the Toggle Filter button to remove the filter or click on Toggle Filter to reapply the filter.
  3. 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.

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.

Recommended Microsoft Access training

Microsoft Access: Introduction

Microsoft Access: Intermediate / Advanced

VIEW MORE COURSES >

Our instructor-led classroom courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada. If you’d like to arrange custom training, contact us to arrange a date.

To request this page in an alternate format, contact us.

Copyright 2019 Avantix® Learning Inc.

You may also like

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 | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Summary
Save Time Running Microsoft Access Reports using Filters In Layout View
Article Name
Save Time Running Microsoft Access Reports using Filters In Layout View
Description
In 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.
Author
Publisher Name
Avantix Learning Inc.

Pin It on Pinterest

Share This