How to Create a Filter in an MS Access Report

Written by darrin koltow
  • Share
  • Tweet
  • Share
  • Email

Creating a filter in a Microsoft Access report can be approached in a number of ways: you can specify a filter on the underlying table, on the report itself or on a query that provides the record source for the report.

Note to users of Access 2003: the following instructions are written for Access 2007 or later, but you can still follow them by making specific substitutions. Wherever the instructions show "Create>Table," "Create>Report" or "Create>Query," do these actions instead: "Objects>Tables>New," "Objects>Reports>New" or "Objects>Query>New."

Skill level:

Things you need

  • Microsoft Access

Show MoreHide


  1. 1

    Inside Access, select "Create>Table" to create a new table. Enter the following data in the table's columns. Press the "tab" key where the data has a comma.

    monopoly,3,$3.98,little rock,arkansas monopoly,4,$3.98,haddonfield,new jersey trivial pursuit,6,$2.48,atlanta,georgia trivial pursuit,2,$1.23,savannah,georgia

  2. 2

    Double-click the column header above the first "monopoly" row and type "game." Type the following headers for the remaining columns in the same way, and in this order:


  3. 3

    Apply a filter: right-click on one of the rows in the "game" column and select "Text filter>contains," then type "ono" without the quotes. Notice that only rows of Monopoly games are shown.

  4. 4

    Create a report from this table: select "Create>Report" and notice that the report only shows the Monopoly games---it has used the filter you applied to the table.

  5. 5

    Remove the filter from the table: right-click on any row in the "game" column again and select "Clear filter from game." Select "Create>Report" to create a new report from the game sales table.

  6. 6

    Create and apply a filter to the report: right-click anywhere on the "game" column and select "Text filter>contains." Type "ono" without the quotes, as you did with the table filter.

  7. 7

    Select "Report" in the "Property Sheet" window, then click the "Data" tab and notice the text in the field "Filter:" (it's your filter criteria). Click inside this "Filter" field and change the "ono" to "ivi". Press the "tab" key to update the report. Notice that the report now shows only rows of "Trivial Pursuit" games.

  8. 8

    Change the report to apply a numeric filter: click inside the "Filter" field again and type the following text. Press the "Enter" key after you enter the text:

    ([gamesales].[nsold] < 5)

    Notice that the report now shows rows matching the new filter (games selling less than 5 units).

  9. 9

    Create a query with the same filter criteria: select "Create>query." Double-click "gamesales" in the "Show table" window, then right-click the query's tab and select "save." Type "reportfilter" for the query's name.

  10. 10

    Drag "nsold" from the "gamesales" window to any column in the query grid. Uncheck the column's check box and type the following in the "Criteria:" field: "<5" (but do not type the quotes).

  11. 11

    Drag the "*" from the "gamesales" window to another column on the query grid, then right-click the query's tab and click "close." Click "OK" when prompted to save the query.

  12. 12

    Select the "reportfilter" query in the Navigation pane, then select "Create>Report" to create a new report, which will have the query you just created as its record source. Notice that the new report has applied the query's filter criteria, showing only games that have sold less than five units.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.