Filter: Limit the number of records in a view or report

This article shows you how to filter records that are displayed in Datasheet view, in Form view, or in a report. For example, in a product list, if you want to find products in the $50 to $100 price range, you can apply a filter instead of having to scan a 100-page list. Filters let you see just the records that you want without changing the design of the objects (such as queries, forms, and reports) in your database.

In addition to filtering, there are a few other techniques that you can use to limit the size of your view or report. Throughout this article, you will find brief descriptions of these techniques and links to articles with more detailed information.

Sections in this topic

Filtering in Access
If you are new to filtering in Access, this section explains the basics.

Examples of filters
This section lists examples that help you define your own filters and explains how to get help from experts on specific or advanced filtering issues.

Work with filters
This section contains the how-to information for working with filters, including saving and removing filters.

More resources
See this section for keyboard shortcuts, filtering commands, more information on troubleshooting filters, and pointers to resources that can get you started with applying filters programmatically.

 Tip   Try Office 2010 In Access 2010, filtering and sorting are easier to find and use with the centrally located commands. Read an article or try Office 2010.

Filtering in Access

ShowWhat a filter does

A filter is a command in Access that helps you to display only those records in a view or report that match one or more criteria. For example, you want to see the products whose price is greater than $50. Instead of opening the Product List query in Datasheet view and scanning the entire list, you can apply a filter.

There are two basic steps to applying a filter. The first step is to identify the column whose values determine whether a record appears in the filtered results. In this example, the Price column is the basis for your filter, because the values in this column must be checked before records are included in the results. The second step is to specify the rule or criterion in the filtered column. To see only those records where the values are greater than 50, you specify the string (also known as the criterion or expression) >50 in the Price column.

Filter criteria and returned data

 Note   You can specify different filters by combining a variety of operators, constants, and functions. You learn how to write filter criteria in the next section, Examples of filters.

When you apply a filter in Datasheet view or Form view, the view is updated to show only the records that match your criteria. If the Product List query shows 200 records, and only 15 products are priced over $50, you see only 15 records after the filter is applied. You can work with the filtered resultset the same way that you work with the initial view — you can edit the data, navigate to different records, and so on. You can remove the filter with a single click of the mouse or add another filter (say, show only those products over $50 that belong to the Beverages category) to further narrow the results.

You can apply a series of filters or specify multiple criteria in a single filter. For example, you can filter for all products with a price greater than $50 and less than $100, as shown in the following table.

Column  Criteria
Price  > 50 and < 100

You can also apply a filter that is based on multiple columns. The filter shown in the following table displays all records where the price is greater than $50 and the product belongs to the Beverages category.

Column  Criteria
Price  >50
Category  "Beverages"

For more information about how to save commonly used filters, how to remove or delete filters, and how to work with filter resultsets, see the section Work with filters.

ShowHow filtering differs from searching

Both filtering and searching help you to focus on a specific subset of your data and eliminate the need to manually scan an entire report or view to find the results you want. However, there is a basic difference between filtering and searching:

  • Use filtering when you want to limit the number of records displayed to only those that match the filtering criteria. Often, fewer records are displayed after a filter is applied. You can save or print the filtered resultset.
  • Use searching when you want to review some or all occurrences of a string in a specific column or in the entire view. Searching does not hide records. It just moves the focus to each occurrence of the search string.

Find dialog box

 Note   The results of your search cannot be saved or printed.

For more information about how to search for a string in Datasheet view or Form view, see the topic Find part or all of a record in a table, query, or form. The tables store the actual data, so you need to open each table individually and then search for the string in the entire Datasheet view for each table.

ShowHow filtering differs from sorting and grouping

Filtering, sorting, and grouping help you to restrict and analyze the data in a view or report, but each of these techniques is different in how it works and when it should be used:

  • Filter a view or report when you are interested in only a portion of the report. For example, in the annual sales report, you want to focus on the sales figures for the regions you are responsible for, so that you can prepare for an annual board meeting. You want to filter out other regions before printing the report.
  • Sort a view or a report when you are interested in all of the data but want the rows arranged in a specific order. For example, you want to see the sales figures for all regions arranged in descending order, so that you can see how your region compares with other regions. For more information about how to sort, see Sort records.
  • Group a data access page or a report when you want the records grouped under different sections and want to summarize each group. For example, you want to group the regions by owner and include subtotals for each region owner.

Though each technique can meet a different need, you can combine the techniques when you present or print data to get just the data you want, shown the way you want it.

ShowHow queries differ from filters

Select queries are similar to filters in that they allow you to display only the records that match your criteria. You can create a select query to retrieve a portion of the data from one or multiple tables or queries. You can choose the fields that should be included in the resultset and can specify one or more criteria for selecting records. But there are situations where one serves your needs better than the other.

Filtering is right for you in the following situations:

  • You want to temporarily narrow your data when working in Datasheet view or Form view     For example, depending on the region where the last sales transaction took place, you want to focus on the figures for that region.
  • The criteria for retrieving data change frequently     For example, you are analyzing the sales report, and you are interested in the top-selling products, the least-selling products, the region with the lowest sales, and so on.
  • You want to narrow the data in a form or a table     Open the form in Form view or the table in Datasheet view and apply a filter.

Creating a query is a better choice than applying a filter in the following situations:

  • The criteria don't change often or are used very frequently    For example, you frequently monitor the sales figures for the regions under your control, so you can create a query to avoid having to filter the sales data each time.
  • The criteria change, but the criteria fields don't    For example, you want to view the products in a specific price range but want the flexibility to specify different ranges. Here, the criteria field is Price, and it doesn't change, but the price range might be different each time. To meet this requirement, you can create a special type of select query, called a "parameter query." A parameter query prompts you to specify the values for the criteria fields before the query is run. This type of query combines flexibility with ease of use. In the following illustration, a dialog box prompts you for parameters before the query is run.

Dialog box prompts you for parameters before retrieving matching records

For more information about creating parameter queries, see Using parameters with queries and reports.

In a more complex scenario where you want to enter parameters for several criteria fields before a query is run, you can build an easy-to-use form that accepts parameters from the user and then passes those parameters to the underlying query. The query then uses the parameters to retrieve matching records. This technique is called "query by form."

Form that accepts parameters for query

For more information about query by form, see How to use the query by form (QBF) technique.

  • You want to include fields from multiple tables and queries in the resultset     For example, you want to view the sales details from the Sales table and the details about the corresponding salespersons from the Employee table.
  • In addition to limiting records, you want to limit the fields that are included in the resultset    For example, you need only the Employee Name and Contact Number fields from the Employee table before making your weekly conference call. Since a filter does not hide fields, you might want to create a query.
  • You want the resultset to include calculated columns and totals     For example, you want the USA Sales query to include the total for an entire region and also a column that calculates the total for each city in that region.

Important    Queries and filters are different, but they are not mutually exclusive — you can and often will use them together. You can start with a query and then apply a filter to narrow the records returned by the query. For example, you open the USA Sales query and apply a filter to see the city with the highest or lowest sales figures. You can also save a filter as a query, if you often find yourself applying the same filter. For more information, see Save a filter as a query.

ShowVarious filtering techniques that are supported in Access

There are four simple techniques that help you to filter a Datasheet view or Form view. (If you want to filter a report, see How to filter a report.)

To... Use...
Return records that contain a string (such as "New York") in the field Filter By Selection
Return records that do not contain a string in the field Filter Excluding Selection

Return records that exactly match a string or the result of a single expression

 Note   An expression is similar to a formula — it evaluates to a single value. To learn more about filtering expressions, see the section Examples of filters.

Filter For
Specify criteria including one or more values or expressions (for example, "Berlin" in the City field or "Germany or France" in the Country field) Filter By Form

For step-by-step information about how to use these techniques, see the section Work with Filters.

In addition, you can filter data by entering complex criteria that include multiple values and multiple fields. You do this by using the Advanced Filter/Sort dialog box. Before you start working in this dialog box, you should be familiar with writing expressions. See the section Examples of filters for more information.

These three techniques  — Filter By Form, Filter For, and Advanced Filter/Sort — accept expressions as criteria. Here is a quick comparison of these techniques:

  • Filter By Form provides a friendly way to specify criteria, but it takes you to a separate window for entering criteria.

Filter by Form window

For step-by-step information about how to use this technique, see Filter by filling out a blank form.

  • Filter For is appropriate when you want to filter quickly, based on a single expression. You can enter an expression and apply the filter without leaving the current view. Unlike the Filter By Form technique, you don't leave the view to specify the expression.

Filter for Input

For step-by-step information about how to use this technique, see Filter records based on one or more expressions.

  • Advanced Filter/Sort displays a dialog box and lets you specify or view a complex set of criteria in a single window. You can combine multiple criteria by using the "And" operator or specify alternate criteria by using the "Or" operator.

Advanced Filter/Sort window

For step-by-step information about how to use this technique, see Filter records based on one or more expressions.

To filter for top or bottom values — such as the region with the highest sales or the 5 percent lowest sales by city — you need to create a query and then specify the criteria by using the Top Values button on the toolbar. For more information, see the topic Show only the high or low values in a query.

ShowHow to filter a report

Limiting the records that are included in a report is a bit different from limiting the records in Datasheet view or Form view. This is because you can't apply a filter to a report in Print Preview or Layout Preview. However, there are ways to reduce the number of records in a report.

If... And... Do this...
The criteria don't change.
For example, you want to create a report that includes sales figures for only USA and Canada.
The report will be based on a single table or query

Open the table or query, apply the necessary filters, and create a report.
When you create a report from a filtered view, the report inherits the filters applied to the underlying recordset. Note that subsequent changes to the filters in the underlying record source do not affect the report.

For step-by-step information, see Save filtered records as a form or report.

The report will be based on more than one table or query

First, create a query that includes just the records and fields that you want to include in the report. Then create a report and specify the new query as its record source.

For step-by-step information, see Create a simple report.

The criteria don't change, but you want to be able to switch between the filtered and unfiltered views before previewing or printing the report.
For example, you want to create a report that includes sales figures for the whole year, but often you print only the data for the current quarter.
The report will be based on a single table or query.

Open the table or query, apply the necessary filters, and then save and close the object. Then create a report based on the object.
When you create a report based on an object that was saved with filters, the report inherits the filters, but the filters are not applied by default. You have the option of applying the filters when you need to. Note that subsequent changes to the filters in the underlying record source do not affect the report.

For step-by-step information about how to apply a filter to a table or query, see Limit records in a Datasheet or Form view. For step-by-step information about how to create a report based on a table or query, see About designing a query (MDB).

The criteria values change often, but not the criteria fields.
For example, you want to filter the annual sales report for figures that pertain to a specific quarter. The criteria are always based on the Quarter field, but the filter value might be 1, 2, 3, or 4.
The report will be based on one or more tables and queries.

Create a parameter query that accepts a value for each criteria field, and then create a report that uses this query as its record source. If the report already exists, modify its RecordSource property to specify the query as its source.
When you open the report, Access runs the underlying parameter query and prompts you to enter the criteria values. The query uses the values to retrieve only those records that match the criteria and passes those records to the report.

For step-by-step information, see the section "Using parameters with reports" in the article Using parameters with queries and reports.

Criteria (both fields and values) change often.
For example, you want to filter the annual sales report on various criteria that change from day to day.
The report will be based on one or more tables and queries.

There are two ways to do this:

Use expressions    If you are comfortable writing expressions in Design view and want the option of switching between the filtered and unfiltered views, set the Filter and FilterOn properties of the report in Design view.

Open a report in Design view, and press F4 to display the report's property sheet. On the Data tab, set the Filter property to an expression and the FilterOn property to Yes. Then switch to Print Preview to see the results of the filter.

Use a form    If you want to create a friendly user interface, you should create a form that accepts parameters for some or all fields in the report and then passes them to a query. Then create a report that binds to the query.


When you open the report, the form accepts the criteria from the user and passes them to the query. The query then retrieves the records that match the criteria and pass them to the report.

For step-by-step information, see the section "Creating a form to supply parameters to a report" in the article Using parameters with queries and reports.

In addition, you can filter a report by using the current filter settings of a form. For example, a form is filtered to show the products by Supplier based on the West coast, and you want to filter the Product Catalog report to print only the products that are displayed on the form. For step-by-step information, see How to filter a report using a form's filter.

To filter a report that contains mailing labels, see the following item, "How to filter mailing labels so not all addresses are printed."

ShowHow to filter mailing labels so not all addresses are printed

You can limit the number of labels in two ways:

  • Specify criteria to view or print only those addresses that meet your needs.
  • Manually scan the list of addresses and choose the ones that you want to view or print.

For step-by-step information, see the section "Select the addresses that you want to print," in the topic Create mailing labels in Access.

ShowFilter a data access page

  • If the criteria don't change, and you want the data access page to include only those records that match your criteria, you can create a query that includes just the fields and records that you want returned and then bind the data access page to the query.

For step-by-step information, see Create a data access page.

  • If the field doesn't change, but you want the flexibility to filter by different values, create a filter control. For example, to see the sales figures for a specific quarter, designate the Quarter text box control as the filter control. In Page view, you can specify the value that you want in this control, such as 1, to retrieve just those records that match your criterion. In this case, you see the data for the first quarter. Note that you do not see any data in the page until the filter is applied.

For step-by-step information, see Filter records based on a field.

  • To filter for records that contain a specific value in a field, use Filter By Selection in Page view.

For step-by-step information, see Filter for a specific value in a field.

  • To filter a data access page by passing parameters from another data access page, create a hyperlink and add a server-side filter to the hyperlink. Then, when you click the hyperlink in Page view or in the Web browser, only the records that are specified in the filter are displayed on the data access page that you go to.

For step-by-step information, see Filter records by passing parameters to a page.

ShowFilter data in a PivotTable view or PivotChart view

You can filter a table, query, or form that is open in either PivotTable or PivotChart view. For information about the various filtering techniques that are available in these views and for step-by-step information, see Filter data in PivotTable or PivotChart view.

ShowFilter data in an Access project

An Access project file connects to a remote Microsoft SQL Server database, a local SQL Server database, or a local installation of SQL Server 2000 Desktop Engine. Unlike databases, project file names use the .adp extension. If you work with Access projects and are interested in filtering a form or report in a project, see the following topics:

ShowDetermine if a view or report is filtered

If you think the view or report is missing some records, it might be filtered or based on a query. Do the following to find out if a view or report is filtered and what criteria are being used to restrict the display of records:

  • In a Datasheet view or Form view, if the Remove Filter Button image button on the toolbar appears selected, the view is filtered.

Tip     Another way to determine if a filter is in effect is to look for the string (Filtered) to the right of the navigation buttons that correspond to the datasheet, subdatasheet, form, or subform.

To see how the report or view is filtered, on the Records menu, point to Filter, and then click Advanced Filter/Sort. In the design grid, the Criteria row and the rows below the Criteria row display the filter criteria.

In Page view, if the Filter Toggle Button image button appears selected, the view is filtered.

In a report, if the FilterOn property of the report is set to Yes, the report is filtered. To view the criteria, see the report's Filter property setting in the report's property sheet in Design view.

  • Check to see if the records are filtered by using Visual Basic for Applications (VBA) code. In a form or report, view the form or report's OnOpen property setting. If the property is set to a macro or an event procedure, open the macro in Design view or open the procedure in the Visual Basic Editor to see if records are being filtered programmatically.
  • Review the record source of the view or report. View the RecordSource property of the object. If it is based on a query, open the query in Design view and review the Criteria row to see if the query restricts the records that are retrieved.

 Note   When working in a shared database, you might want to update the filtered view periodically. For information about how to update your view, see Update your filtered view.

ShowHow filters are saved and reapplied

  • Tables    When you save a table, the last created filter is saved. However, the filter is not automatically applied the next time that you open the table. If you want the filtered view, click the Apply Filter button on the toolbar.
  • Queries    When you save a query, the last created filter is saved. However, the filter is not automatically applied the next time that you open the query. If you want the filtered view, click the Apply Filter button on the toolbar.
  • Forms and subforms    When you save a form or a subform, the last created filter is saved. However, the filter is not automatically applied the next time that you open the form or subform. If you want the filtered view, click the Apply Filter button on the toolbar. Note that in the case of a subform, the behavior is exactly the same, whether you open the subform independently or by opening the main form.

When you create a form that is based on an open table or query that is filtered, the filter is saved and automatically applied the first time that you open the form after you created it. Subsequently, you must apply the filter yourself. When you create a form that is based on a closed table or query, the filter settings that are saved with the table or query are also saved with the new form but are not automatically applied when you open the form the first time after you created it.

  • Reports    When you create a report that is based on an open table or query that is filtered, the filter is saved and automatically applied to the report each time that you open it. When you create a report that is based on a closed table or query, the filter settings that are saved with the table or query are also saved with the new report but not automatically applied when you open the report. You must set the FilterOn property to Yes in Design view to apply the filter.

Note that you can use VBA code to force a saved filter to be applied automatically at the time of opening a form or report. For step-by-step information, see Apply a filter when a form or report opens.

 Important   To see the expression behind a saved filter in Datasheet view or Form view, on the Records menu, point to Filter and then click Advanced Filter/Sort. You see a window that is similar to the query design grid. The Criteria row in the grid displays the filter expressions. To see the saved filter in a report, display the report's property sheet, and note the string that is assigned to the Filter property.

In Datasheet view or Form view, the saved filter is lost when you apply a different filter during the current session.

ShowSave multiple filters with a table, query, or form

Only the most recent filter combination is saved in a Datasheet view or Form view. However, if you frequently apply certain filters, you can save them each as a query. Subsequently, you can apply one of these queries as a filter in Datasheet view or Form view. For step-by-step information, see Save a filter as a query and Apply a query as a filter.

ShowWhen a new filter is applied to a view that is already filtered

If you add a filter by using Filter By Selection, Filter For, or Filter Excluding Selection, the filter is combined with the existing filters (regardless of which technique was used to apply the new filter) to further restrict the view. To start anew, remove all filters by clicking Remove Filter on the toolbar, and then apply the filters that you want.

If you use Filter By Form or Advanced Filter/Sort, you see the existing filter criteria in the fields, and you can either specify additional criteria or change the existing criteria. When you click Apply Filter, all previous filters are replaced with the filter criteria that were specified in the Filter By Form window or the design grid before you clicked Apply Filter.

In a report, the report's Filter property setting defines the current filters. When you modify this property, Access replaces the previous filter with the current one.

ShowFilter a combo box

You might want to limit the items that are listed in a combo box based on what is selected or displayed in another control, such as a text box or even another combo box.


Back to top  Back to top

Examples of filters

This section explains how to create filter criteria, also known as expressions. An expression is similar to a formula — it is a string that may consist of field references, operators, and constants.

For example, here are some simple expressions that filter a view:

  • To show only records where the price is between 50 and 100, in the Price field, you enter:
    > 50 and < 100
  • To show only records where the last name equals "Smith," in the LastName field, you enter:
    Smith

If you are using Filter By Selection or Filter Excluding Selection, you do not need to write an expression. However, to filter by using Filter For, Filter By Form, or Advanced Filter/Sort, you need to create an expression that specifies how to filter records. If you are using the Filter property of a report to filter records, you need to specify an expression.

Where to enter the filter criteria

The following illustration shows where you specify the expression. In a Filter By Form operation, you enter the expression in a data field (such as Category). In a Filter For operation, you enter the expression in a box next to the Filter For menu item. In an Advanced Filter/Sort operation, you enter the expression in a query cell (such as Criteria).

Different filter expressions are typed in different fields

The tables in the following sections contain lists of several filter expressions. You will see that these expressions use a variety of operators, functions, and characters with special meaning — also known as wildcard characters. Near the end of this section, you will see links to topics that explain how to write expressions and how to use operators, functions, and wildcard characters.

ShowFilter text and memo fields

The following examples are for the Country field in the Sales query.

To... Use this criterion... To display
Filter for records with exact matches China Sales records for China.
Tip    To do this type of filtering faster, use Filter By Selection.
Filter for records with values other than the specified string Not Mexico Sales records for all countries/regions other than Mexico.
Tip    To do this type of filtering faster, use Filter Excluding Selection.
Filter for records where the field value begins with the specified string U*

Sales records for all countries/regions whose names start with "U", such as UK, USA, and so on.

 Note   The character * when used in an expression represents any string of characters — it is also called a wildcard character. For a list of such characters, see About using wildcard characters.

Filter for records where the field value does not start with the specified string Not U* Sales records for all countries/regions whose names start with a character other than "U".
Filter for records that contain the specified string Like "*Korea*" Sales records for all countries/regions that contain the string "Korea".
Filter for records that do not contain the specified string Not Like "*Korea*" Sales records for all countries/regions that do not contain the string "Korea".
Filter for records that end with the specified string Like "*ina" Sales records for all countries/regions whose names end in "ina", such as China and Argentina.
Filter for a range of values >= "Mexico" Sales records of all countries/regions, beginning with Mexico and continuing through the end of the alphabet.
Filter for a specific range Like "[A-D]*" Sales records for countries/regions whose names start with the letters "A" through "D".
Filter for multiple values "USA" Or "UK" Sales records for USA and UK.
Filter for a list of values In("France", "China", "Germany", "Japan") Sales records for all countries/regions specified in the list.
Filter based on the position of the specified string in the field value Right([Country], 1) = "y" Sales records for all countries/regions where the last letter is "y".
Filter based on the length of the field values Len([Country]) > 10 Sales records for countries/regions whose name is more than 10 characters long.
Filter for patterns Like "Chi??"

Sales records for countries/regions, such as China and Chile, whose names are five characters long and the first three characters are "Chi".

 Note   The characters ? and _, when used in an expression, represent a single character — these are also called wildcard characters.

Filter for null (or missing) values Is Null Sales records where there is no value in the field.
Filter for non-null values Is Not Null Sales records where the value is not missing in the field.
Filter for blanks "" (a pair of double quotes) Sales records where the field is set to a blank (but not null) value. For example, records of sales made to another department might contain a blank value in the Country field.
Filter for nonblanks Not "" Sales records where the Country field has a nonblank value.

ShowFilter number, currency, and autonumber fields

The following examples are for the Price field in the Sales query.

To... Use this criterion... To display
Filter for records with exact matches 100 Sales records where the price of the product sold is $100.
Tip    To do this type of filtering faster, use Filter By Selection.
Filter for records with values other than the specified value Not 1000 Sales records where the price of the product sold is not $1000.
Tip    To do this type of filtering faster, use Filter Excluding Selection.
Filter for values smaller than the specified value < 100
<= 100
Sales records where the price is less than $100 (<100). The second expression (<=100) displays sales records where the price is less than or equal to $100.
Filter for values larger than the specified value >99.99
>=99.99
Sales records where the price is greater than $99.99 (>99.99). The second expression displays sales records where the price is greater than or equal to $99.99.
Filter for multiple values 20 or 25 Sales records where the price is either $20 or $25.
Filter for a range of values >49.99 and <99.99
-or-
Between 50 and 100
Sales records where the price is between (but not including) $50 and $100.
Filter for values outside a range <50 or >100 Sales records where the price is not between $50 and $100.
Filter for a list of values In(20, 25, 30) Sales records where the price is either $20, $25, or $30.
Filter for records that end with the specified numbers Like "*4.99"

Sales records where the price ends with "4.99", such as $4.99, $14.99, $24.99, and so on.

 Note   The characters * and %, when used in an expression, represent any number of characters — these are also called wildcard characters.

Filter for null (or missing) values Is Null Sales records where no value is entered in the Price field.
Filter for non-null values Is Not Null Sales records where the value is not missing in the Price field.

ShowFilter date and time fields

The following examples are for the SalesDate field in the Sales query. Note that the date or time format that you use to specify the criteria do not need to match the format in which the value is stored in the database.

To... Use this criterion... To display
Filter for records with exact matches #2/2/2006# Sales records of transactions that took place on Feb 2, 2006. Remember to surround date values with the # character so that Access can distinguish between date values and text strings.
Tip    To do this type of filtering faster, use Filter By Selection.
Filter for records with today's date Date() Sales records of transactions that took place on the current day. If today's date is 2/2/2006, you see records where the SalesDate field is set to Feb 2, 2006.
Filter for records with yesterday's date Date()-1 Sales records of transactions that took place the day before the current day. If today's date is 2/2/2006, you see records for Feb 1, 2006.
Filter for the past 7 days' records Between Date() and Date()-6 Sales records of transactions that took place during the last 7 days. If today's date is 2/2/2006, you see records for the period Jan 24, 2006, through Feb 2, 2006.
Filter for current month's records Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now()) Sales records for the current month. If today's date is 2/2/2006, you see records for Feb 2006.
Filter for previous month's records Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now()) - 1 Sales records for the previous month. If today's date is 2/2/2006, you see records for Jan 2006.
Filter for records for the preceding 30 or 31 days Between Date( ) And DateAdd("M", -1, Date( )) A month's worth of sales records. If today's date is 2/2/2006, you see records for the period Jan 2, 2006, to Feb 2, 2006.
Filter for current quarter's records Year([SalesDate]) = Year(Now()) And DatePart("q", [SalesDate]) = DatePart("q", Now()) Sales records for the current quarter. If today's date is 2/2/2006, you see records for the first quarter of 2006.
Filter for "year to date" records Year([SalesDate]) = Year(Now()) Sales records of transactions that have taken place since Jan 1 of the current year. If today's date is 2/2/2006, you see records for the period Jan 1, 2006, to 2/2/2006.
Filter for last year's records Year([SalesDate]) = Year(Now()) - 1 Sales records of transactions that took place during the previous year. If today's date is 2/2/2006, you see records for the year 2005.
Filter for records with values other than the specified value Not #2/2/2006# Sales records of transactions that place on a day other than Feb 2, 2006.
Tip    To do this type of filtering faster, use Filter Excluding Selection.
Filter for values older than the specified value < #2/2/2006#

Sales records of transactions that took place before Feb 2, 2006.

To view transactions that took place on or before this date, use the <= operator instead of the < operator.

Filter for values newer than the specified value > #2/2/2006#

Sales records of transactions that took place after Feb 2, 2006.

To view transactions that took place on or after this date, use the >= operator instead of the > operator.

Filter for multiple values #2/2/2006# or #2/3/2006# Sales records of transactions that took place on either Feb 2, 2006, or Feb 3, 2006.
Filter for a range of values >#2/2/2006# and <#2/4/2006#

Sales records where the transactions took place between Feb 2, 2006, and Feb 4, 2006.

You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2006# and #2/4/2006# is the same as >=#2/2/2006# and <=#2/4/2006#.

Filter for values outside a range <#2/2/2006# or >#2/4/2006# Sales records where the transactions took place before Feb 2, 2006, or after Feb 4, 2006.
Filter for a list of values In (#2/1/2006#, #3/1/2006#, #4/1/2006#) Sales records where the transactions took place on Feb 1, 2006, March 1, 2006, or April 1, 2006.
Filter for null (or missing) values Is Null Sales records where the date of transaction is missing.
Filter for non-null values Is Not Null Sales records where the date of transaction is known.

ShowFilter Yes/No and hyperlink fields

Yes/No    If you are using either Filter For or Advanced Filter/Sort, type Yes or No in the text entry box. If you are using Filter By Form, you see a check box in the field. Select or clear the check box to find matching records. If you are setting the Filter property of a report, compare the field to "Yes" or "No", such as Employee.Married = Yes.

Hyperlink    You can filter a hyperlink field based on the display text of the hyperlinks. You cannot filter based on the destination URL. Several of the common filters that apply to the text data type work on a hyperlink field. See the first table for sample expressions.

ShowFilter for top or bottom items

To filter for top or bottom items based on the values in a field, you need to create a top values query. For example, to see the five cities with the highest sales figures or the bottom 1 percent of salespersons with the lowest sales figures, you need to create a query and specify the criteria in the Top Values box in Query Designview. For step-by-step information, see Show only the high or low values in a query.

ShowFilter for unique or duplicate values

To see if more than one record contains a specific value in a particular field, use Filter By Selection. However, to find duplicate records, and hide or delete them, see the topic Find, eliminate, or hide duplicate records in Access.

Learn how to write expressions

To learn more about writing expressions and to see the full list of operators, functions, and wildcard characters, see the following topics:

Create an expression

About using wildcard characters

Functions (by category)

Still looking for help?

Didn't find the expression you are looking for? Does an expression just not work as expected? Or do you need help creating a complex filter? See the Work with Filters section later in this article, and the Troubleshoot filters topic. If you're still stuck, you can get help from communities on the Web — by posting a question to Access experts and other users.

Learn about communities with Ask the Access community. If you want to learn more about posting questions to communities, see Start getting answers from other Office users.


Back to top  Back to top

Work with filters

This section gives step-by-step procedures to help you limit the records that are returned in your view or report. Although several of the procedures describe how to work with filters, a few describe other techniques, such as creating top values queries and reports based on parameter queries.

Limit records in a Datasheet or Form view

 Note   If the filter commands appear dimmed in a Datasheet view or Form view, the designer of the form or database might have disabled filtering. Contact the designer for further assistance.

ShowFilter for records that contain a value that wholly or partially matches a specific value

Do this by using Filter By Selection.

  1. In a field on a form, subform, datasheet, or subdatasheet (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.), find one instance of the value (in a field or in a control) that you want to be used as a criterion for the filter's results.
  2. Select all or part of a value in a field. Note that how you select the value determines which records the filter returns.
    • Exact match    To find exact matches, select the entire contents of a field. For example, select the value Berlin in the City field to return all records with Berlin as the city. To filter based on the status of a check box, move the focus to the cell without selecting the actual check box in the cell.
    • Starts with    To find records that start with the same characters as the chosen instance, select the first n number of characters. For example, select only the first character "U" in the field value "USA" to filter for all countries/regions whose names start with "U".
    • Ends with    To find records that end with the same characters as the chosen instance, select the last n number of characters. For example, select only the last three characters "ina" in the field value "China" to filter for all countries/regions whose names end with "ina", such as China and Argentina.
    • Contains    To find records in which all or any part of the value in the field matches the characters that you selected, select part of a value starting after the first character in a field.
  3. Click Filter By Selection Button image on the toolbar.

ShowFilter for records that do not match, contain, start with, or end with a specific value.

Do this by using Filter Excluding Selection.

  1. In a field on a form, subform, datasheet, or subdatasheet (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.), find one instance (in a field or in a control) of the value that you want to exclude from the filter's results.
  2. Select all or part of a value in a field. Note that how you select the value determines what records the filter returns.
    • Exclude exact matches    To exclude exact matches, select the entire contents of a field. For example, select the value Berlin in the City field to return all records with a value other than Berlin as the city. To filter based on the status of a check box, move the focus to the cell without selecting the actual check box in the cell.
    • Does not start with    To find records that start with characters other than those of the chosen instance, select the first n number of characters. For example, select only the first character "U" in the field value "USA" to filter for all countries/regions whose names do not start with "U".
    • Does not end with    To find records that end with characters other than those of the chosen instance, select the last n number of characters. For example, select the last three characters "ina" in the field value "China" to filter for all countries/regions whose names do not end with "ina".
    • Does not contain    To exclude records in which all or any part of the value in the field matches the characters that you selected, select part of a value starting after the first character in a field.
  3. Click Filter Excluding Selection on the toolbar.

ShowFilter for records with top or bottom n or n% values

ShowFilter for unique or duplicate values or records

ShowFilter by filling out a blank form

Do this by using Filter By Form.

  1. In Form view or Datasheet view, click Filter By Form Button image on the toolbar. If you want to filter a subdatasheet, expand at least one subdatasheet before clicking Filter By Form.
  2. You see a blank form or datasheet, as shown in the following illustration. Click the field in which you want to specify the criteria. The criteria fields can be in a form, subform, datasheet, or any subdatasheet.

Blank form

  1. To filter for records with values that exactly match a value in a field with a data type of Text, Memo, Number, Currency, Hyperlink, or Date/Time, specify a criterion by selecting the value from the list in the field (if the list includes field values) or by typing a value into the field.

ShowIf you do not see a list of field values when you click the drop-down arrow of a field

If you see only the values Is Null and Is Not Null displayed when you click the drop-down arrow, do the following to display the actual field values:

  • Verify that the data type for the field is not Memo, Hyperlink, Yes/No, or OLE Object. Fields of these data types cannot be made to display field values in the Filter By Form window.
  • If the number of records read is more than the number specified in the Don't display lists where more than this number of records read text box on the Edit/Find tab of the Options dialog box, Access doesn't display values for that field on the list. Consequently, you need to change the setting for this option.
  • On the Tools menu, click Options. On the Edit/Find tab, under Show list of values in, select the Local indexed fields and Local nonindexed fields check boxes. Then switch back to Datasheet view or Form view.

To filter for partial matches or for a range of values in a Text, Memo, Number, Currency, Hyperlink, or Date/Time field, type an expression in the field. For more information about writing expressions, see the section Examples of filters.

To filter based on the status of a control, such as a check box or button, click the control so that it is in the state that you want. To return it to a neutral position so that it won't be used as a criterion for filtering records, ensure that it is unavailable (appears dimmed).

To filter for records with null (missing), non-null, blank (empty or ""), or nonblank values, type Is Null, Is Not Null, "", or Not "" in the field.

  1. Repeat the previous step to specify a criterion in each of the criteria fields. Remember that when you specify criteria in more than one field, Access automatically combines them. In other words, the resultset includes only those records that meet each and every single field criterion.

To retrieve records that meet multiple criteria, you need to specify the criteria somewhat differently. Note the Look for tab and the Or tab at the bottom of the Filter By Form window, as shown in the following illustration.

Although the criteria specified on the Look for tab are applied together, criteria specified on the Or tab are treated as an alternative. In other words, Access retrieves the records as long as the records match just one set of criteria — either the criteria specified on the Look for tab or the criteria specified on the Or tab.

Alternate criteria on Look for and Or tabs in Filter by Form window

Note that when you click the Or tab, Access creates a second Or tab. This is to enable you to specify several "or" criteria. The filter returns records if they have all the values specified on the Look for tab, or all the values specified on the first Or tab, or all the values specified on the second Or tab, and so on.

  1. Click Apply Filter Button image on the toolbar.

ShowFilter records based on one or more expressions (in Datasheet view or Form view)

Do this by using either Filter For or Advanced Filter/Sort. Note that you can also specify expressions in the Filter By Form window. To learn when to use which technique, see the comparison of the three techniques. To learn how to write expressions, see Examples of filter expressions. To apply a filter by using Filter By Form, see the previous procedure.

To filter based on a single expression, use Filter For.

  1. In Datasheet view or Form view, right-click the criteria field in the form, datasheet, subform, or subdatasheet. In the Filter For box, type the complete expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.), using the appropriate combination of values, operators, wildcard characters, and functions to produce the results you want. For example, to find all records where the Country field contains "Korea", type *Korea* in the Filter For box.
  2. Press ENTER to apply the filter. This closes the shortcut menu and updates the view to show just the filtered records.

To filter based on multiple expressions, use Advanced Filter/Sort.

  1. In Datasheet view or Form view, on the Records menu, point to Filter, and then click Advanced Filter/Sort. To filter based on a subform or subdatasheet, make sure the focus is on the subform or subdatasheet.
  2. Drag the criteria fields to the design grid.
  3. In the Criteria cell for the fields you included, enter the expressions. The expressions are applied as a set, and only records that match all the expressions in the Criteria row are displayed. To specify alternate criteria, type the first set of criteria in the Criteria row, the second set of criteria in the or row, and the others in the rows below the or row.
  4. Apply the filter by clicking Apply Filter Button image on the toolbar.

Tip    A good way to learn this technique is to first apply a filter by using Filter By Selection or Filter By Form, pointing to Filter on the Records menu, and then clicking Advanced Filter/Sort. The filter looks similar to a query — and you can then modify or add to it.

Limit records in a report

ShowFilter records in a report

You can filter a report in several ways. For information about choosing the right technique and step-by-step information, see How to filter a report.

ShowFilter mailing labels before they are printed

Limit records in Page view

ShowFilter records in a data access page that is based on a single field

Do this by creating a group filter control. This control displays a list of field values. Based on selection, this control restricts the records to only those that contain the selected value. The important difference between this technique and Filter By Selection is that when a data access page has a group filter control, no data is displayed until after the filter is applied.

  1. Open the data access page in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. If the fields that you want to display aren't in the section (section: A part of a form, report, or data access page such as a header, footer, or detail section.) already, add them to the page.
  3. Add the field that you want to use for filtering as a text box, bound span, drop-down list box, or list box.
  4. On a grouped page, select the field control, and then click Promote Button image on the toolbar to group only on that field. To group on the whole table or on the query that contains the field, click Group by Table Button image on the toolbar. Note that if you used the Page Wizard to create the data access page, the Group by Table option is not available.

Access adds a group header (group header: Used to place information, such as group name or group total, at the beginning of a group of records.) with an expand control (expand control: A control on a data access page that, when clicked, expands or collapses a grouped record to display or hide its detail records.) and a record navigation section (record navigation section: A section in a data access page that displays the record navigation control for a specific group level. It appears after the corresponding group header section. You can't place bound controls in a record navigation section.) with a record navigation control (record navigation control: A control used on a data access page to display a record navigation toolbar. In a grouped page, you can add a navigation toolbar to each group level. You can customize the record navigation control by changing its properties.). If you group on one field, Access moves the control on which you grouped to the group header. If you grouped on a table or query, Access moves all the controls — including controls bound to fields that are in that table or query and controls bound to Lookup fields (Lookup field: A field, used on a form or report in an Access database, that either displays a list of values retrieved from a table or query, or stores a static set of values.) — to the group header.

  1. Right-click the filter field control and then click Group Filter Control.

ShowOptional information for an ungrouped data access page

If necessary, customize the record navigation control. For example, on a data access page that finds one record at a time, not all of the buttons are required, so you might want to show only the New, Delete, Save, Undo, and Help buttons on the record navigation toolbar.

When you create a group filter control, the GroupFilterField and GroupFilterControl properties are automatically set for the group level (group level: The depth at which a group in a report or data access page is nested inside other groups. Groups are nested when a set of records is grouped by more than one field, expression, or group record source.).

If you convert a text box control or a bound span control (bound span control: A control that is used on a data access page to bind HTML code to a Text or Memo field in an Access database or to a text, ntext, or varchar column in an Access project. You cannot edit the contents of a bound span control.) to a group filter control, the group filter control changes to a drop-down list box control, and the following control properties are set: ListRowSource, ListBoundField, and ListDisplayField.

If you add a group filter control to a group level that already has a group filter control, the original control changes to a simple drop-down list box control. To manually convert a group filter control to a drop-down list box, right-click the control and then click Group Filter Control.

ShowOptional information for a grouped data access page

You might want to delete or modify some of the standard elements of the data access page. For example, select the expand control in the header that contains the filter control, and then press DELETE. Select the record navigation section for the group level (group level: The depth at which a group in a report or data access page is nested inside other groups. Groups are nested when a set of records is grouped by more than one field, expression, or group record source.) that contains the filter control, and then press DELETE to delete the section and the record navigation control that it contains. You can customize the record navigation control for the lower group level — for example, you can hide the buttons that are used to add and delete records.

If the group header also contains a control that calculates a total or other aggregate, when you choose a group from the group filter, the calculated control displays the correct total or other aggregate for that group of records.

You can have multiple group levels, each with one group filter. For example, you can create a group filter for the Country field and then in the next lower group level, create a group filter for the City field, and so on.

When you create a group filter control, the GroupFilterField and GroupFilterControl properties are automatically set for the group level. If you convert a text box control or a bound span control (bound span control: A control that is used on a data access page to bind HTML code to a Text or Memo field in an Access database or to a text, ntext, or varchar column in an Access project. You cannot edit the contents of a bound span control.) to a group filter control, the group filter control changes to a drop-down list box control, and the following control properties are set: ListRowSource, ListDisplayField, and ListBoundField.

If you add a group filter control to a group level that already has one, the original control changes to a drop-down list box control. To manually convert a group filter control to a drop-down list box control, right-click the control and then click Group Filter Control. The control changes to a drop-down list box.

ShowFilter a page for specific values in one or more fields

You do this by using Filter By Selection.

  1. In Page view or in the Web browser, move the focus to the field that has the value that you want to use to filter records. If the data access page is grouped, expand the group to display the field.
  2. Click Filter By Selection Button image on the record navigation toolbar.

Note that the filter applies only to the current group. In other words, if a page groups sales by country/region, and you filter the records for Brazil that have a sales date of 6/2/06, that filter applies only to the records for Brazil, not to the records for the other countries/regions.

To further narrow the subset of records, continue selecting fields and clicking Filter By Selection Button image until you have just the records that you want. You cannot filter for a portion of a field value. The data access page always uses the entire field value for filtering.

This procedure doesn't apply to a PivotTable list (PivotTable list: A Microsoft Office Web Component that is used to analyze data interactively on a Web page. Data displayed in a row and column format can be moved, filtered, sorted, and calculated in ways that are meaningful for your audience.), Spreadsheet Component (Spreadsheet Component: A Microsoft Office Web Component that provides the interactive functionality of a spreadsheet on a Web page.You can enter data, add formulas and functions, apply filters, change formatting, and recalculate.), or Chart Component (Chart Component: A Microsoft Office Web Component that provides interactive charting functionality on a Web page. You can illustrate data in a chart and give users the ability to change data, change chart options, and see changes reflected in the chart.). These features can be filtered independently from the rest of the data on the data access page and may also support additional filtering capabilities. For more information, click Help Button image on that component's toolbar.

ShowFilter records on a data access page by passing parameters from another page

  1. Open a data access page from which you want to pass parameters in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. On the Insert menu, click Hyperlink.
  3. In the Insert Hyperlink dialog box, click Page in this database under Link to.
  4. In the Text to display box, type the text that you want to be displayed in the field or text box. If you leave the box blank, Access uses the hyperlink address as the display text.
  5. Click ScreenTip and type the text that you want to appear when the user rests the pointer on the hyperlink. If you leave the ScreenTip text box blank, Access displays the hyperlink address as the ScreenTip.
  6. From the list, select the data access page you want to link to.
  7. To create a filter for the data access page, click Server Filter and type an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) in the form of a WHERE clause (WHERE clause: The part of an SQL statement that specifies which records to retrieve.) in the Filter criteria for the data access page box. For example, if you have a data access page that displays order information, including the ID of the salesperson, and you want to link to a data access page with the employee record about that salesperson, type EmployeeID=[EmployeeID].
  8. Click OK twice.

Access adds the hyperlink to the data access page. To test the link, switch to Page view (Page view: An Access window in which you can browse the contents of a data access page. Pages have the same functionality in Page view as in Microsoft Internet Explorer 5.0 or later.) and click the hyperlink. Access opens the target page in your Web browser.

Limit records in PivotTable or PivotChart view

ShowFilter records in PivotTable or PivotChart view

Limit records in an Access project (files with .adp extensions)

ShowFilter records in an Access project (ADP file)

Save a filter as a query, form, or report

ShowSave a filter as a query

Because only a single filter is saved with a table, query, or form, you can save additional filters as queries. You can then use these queries as filters and also as a record source for creating other forms and reports.

  1. With the filter displayed in the Filter By Form window (Filter By Form: A technique for filtering data that uses a version of the current form or datasheet with empty fields in which you can type the values that you want the filtered records to contain.) or the Advanced Filter/Sort window (Advanced Filter/Sort window: A window in which you can create a filter from scratch. You enter criteria expressions in the filter design grid to restrict the records in the open form or datasheet to a subset of records that meet the criteria.), click Save As Query Button image on the toolbar.
  2. Type a name for the query, and then click OK.

The new query appears in the Database window. It automatically includes all the fields from the underlying view, because the query's OutputAllFields property is automatically set to Yes — you can change the query's design, if you want.

For step-by-step information about applying a query as a filter, see Apply a query as a filter.

ShowSave filtered records as a form or report

With the filtered records displayed, click the arrow next to the New Object button Button image on the toolbar, and then click AutoForm or AutoReport.

The new object uses as its record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) the table or query in which the filter was created, and the object inherits the filter. The filter is applied automatically each time that you view the report, but it is applied only the first time that you view the form after you create it. The next time you open the form, you need to apply the filter yourself.

If you subsequently change the filter in a table or query, it has no effect on the form or report.

Apply an existing or saved filter

ShowApply an existing or saved filter

In a table, query, or form    In Datasheet view or Form view, click Apply Filter Button image on the toolbar to apply the most recently used filter.

On a data access page    In Page view, you can reapply a filter that was created during the current working session. On an ungrouped page, click Filter Toggle Button image on the record navigation toolbar so that it is selected. On a grouped page (grouped data access page: A data access page that has two or more group levels.), expand the group that you want to filter, and then click Filter Toggle on the record navigation toolbar for that group so that the button is selected.

 Note   This procedure doesn't apply to a Microsoft Office PivotTable Component, Spreadsheet Component, or Chart Component. These features can be filtered independently from the rest of the data on the data access page and may also support additional filtering capabilities. For more information, click Help Button image on that component's toolbar.

In a report    To apply the filter that the report inherited from the underlying table or query at the time of creation, open the report in Design view, and in the report's property sheet, set the FilterOn property to Yes.

ShowApply a saved filter automatically so that the form or report is already filtered when it opens

To automatically apply a saved filter to a form or report, set the form's OnOpen property to a macro that uses the ApplyFilter action or to an event procedure that uses the ApplyFilter method of the DoCmd object. For more information about using the ApplyFilter action or method, see ApplyFilter Action or ApplyFilter Method.

ShowApply a query as a filter in a Datasheet view or Form view

Although you can save only one filter for each form, subform, datasheet, or subdatasheet (subdatasheet: A datasheet that is nested within another datasheet and that contains data related or joined to the first datasheet.), you can use queries that you already created as additional filters. If such a query doesn't exist, you must first create it. For step-by-step information on how to save a filter as a query, see Save a filter as a query.

  1. In Datasheet view or Form view, click Filter By Form on the toolbar, or, on the Records menu, point to Filter and then click Advanced Filter/Sort.
  2. On the toolbar, click Load From Query Button image.
  3. In the Filter box, select a query, and then click OK.

 Note   Only select queries that are based on the same underlying table or query as the form or datasheet will appear in the dialog box. Action queries and select queries that include aggregate values cannot be applied as filters.

  1. Click Apply Filter Button image on the toolbar.

Remove, modify, or delete filters

ShowRemove filters (to see all records)

Remove a filter either to see all records or to apply a set of filters that are different from the current ones. Note that you cannot remove a portion of a filter. In other words, if you filter by City and then by Date, you cannot remove the filter only on the Date field or the City field. To filter by only one field, you must remove the entire filter and then apply a new filter based on a single field.

In Datasheet view or Form view, click Remove Filter Button image on the toolbar. If you are working on a shared database, click Remove Filter/Sort on the Records menu to remove filters and then immediately update your view. Note that removing a filter from a datasheet removes filters from the subdatasheet, as well.

In a report, in Design view, set the FilterOn property of the report to No.

In Page view, click Filter Toggle Button image on the record navigation toolbar so that it is not selected.

To remove filters that were applied programmatically, review the code that is attached to the OnOpen event of a form or report.

If, after all these steps, you still don't see the records that you are looking for, review the record source that the view or report is bound to. In the case of a shared database, find out if other users made changes that are affecting your view or report.

ShowSwitch between filtered and unfiltered views

In Datasheet view or Page view, click Apply Filter on the toolbar to switch between filtered states. If the button appears selected, the view is in the filtered state. Click again to view all records.

In a report, in Design view, set the report's FilterOn property to Yes or No to switch between filtered states.

In Page view, click Filter Toggle on the record navigation toolbar to switch between filtered states. If the button appears selected, the view is in the filtered state. Click again to view all records.

ShowModify a filter

If you see too few or too many records after you filter the view or report, you can do one of the following:

  • Remove the current filters, and then apply a different set of filters.
  • Add more filters to the already filtered view. For example, add another criterion by using Filter By Selection or Filter By Form.
  • Remove some of the expressions to make the criteria less restricting. In the Advanced Filter/Sort dialog box, either remove one or more criteria fields, or modify the expression that is specified in the Criteria cell. Also, if your intention is to specify alternate criteria, make sure that not all expressions are specified in the Criteria row. Alternate criteria must be specified in the Criteria and or rows.
  • In a report, modify the value of the Filter property of the report.
  • If the view or report is being filtered programmatically, review the VBA code that is attached to the OnOpen event of a form or report.

ShowDelete a filter (permanently delete current or saved filters)

In Datasheet view or Form view, on the Records menu, point to Filter, and then click Advanced Filter/Sort. To delete all filters, on the Edit menu, click Clear Grid. To delete specific criteria, clear the corresponding cells. Then click Apply Filter on the toolbar.

In a report, delete the value of the report's Filter property, and set the FilterOn property of the report to No.

Other filter-related tasks

ShowUpdate your filtered view when you work with shared databases

If you're working in a filtered view in a multiuser (shared) database (multiuser (shared) database: A database that permits more than one user to access and modify the same set of data at the same time.), you may want to reapply the filter occasionally to see which records have been added, deleted, or changed since you last opened the form or datasheet and applied the filter.

In the case of a data access page or a report, just close and reopen the page or report.


Back to top  Back to top

More resources

This section describes the keyboard shortcuts that are used with filtering, contains additional information about troubleshooting filters, and helps you to get started with using Visual Basic for Applications (VBA) code to programmatically work with filters.

Keyboard shortcuts

The following table lists keyboard shortcuts for frequently performed actions when working with filters.

To do this... Press...
Switch between the upper and lower portions of the Advanced Filter/Sort window F6
Move between columns or fields TAB or SHIFT+TAB
Move between rows in the Advanced Filter/Sort window UP and DOWN ARROW keys
Open the list for the currently selected field F4
Move to the next or previous item in the drop-down list for a field Arrow keys
Close the drop-down list for a field and apply any changes you made ENTER
Close the drop-down list for a field without applying your changes ESC
Select or clear the check box for the current item in the drop-down list for a field SPACEBAR

Troubleshooting

For help with troubleshooting issues related to filtering in Access, see Troubleshoot filters.

Programming topics

The following topics can get you started with applying filters programmatically by using VBA code. If you are new to writing macros or code, see the topics Create a macro and Create a Visual Basic procedure. If you need help with specific requirements or with debugging your code, you can get help from Access experts in the Web community. For more information, see the section Still looking for help?.

  • To apply filters by using macros, create a macro that uses the ApplyFilter action.

For more information about how to use this action, see the topic ApplyFilter Action.

  • To apply filters by using VBA, create an event procedure that calls the ApplyFilter method.

For more information about how to use this method, see the topic ApplyFilter Method.

You can also set the Filter and FilterOn properties of a form or report programmatically. For more information, see the following VBA Help topics:

Back to top  Back to top

 
 
Applies to:
Access 2003