Filter Property

Applies to

Form Object
Report Object

You can use the Filter property to specify a subset of records to be displayed when a filter is applied to a form (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.), report (report: An Access database object that you can print, which contains information that is formatted and organized according to your specifications. Examples of reports are sales summaries, phone lists, and mailing labels.) query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.), or table (table: A database object that stores data in records (rows) and fields (columns). The data is usually about a particular category of things, such as employees or orders.). Read/write String.


expression Required. An expression that returns one of the objects in the Applies To list.


If you want to specify a server filter within a Microsoft Access project (Microsoft Access project: An Access file that connects to a Microsoft SQL Server database and is used to create client/server applications. A project file doesn't contain any data or data-definition-based objects, such as tables and views.) (.adp) for data located on a server, use the ServerFilter property.

The Filter property is a string expression (string expression: An expression that evaluates to a sequence of contiguous characters. Elements of the expression can be: functions that return a string or a string Variant (VarType 8); a string literal, constant, variable, or Variant.) consisting of a WHERE (WHERE clause: The part of an SQL statement that specifies which records to retrieve.) clause without the WHERE keyword. For example, the following Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) code defines and applies a filter to show only customers from the USA:

Me.Filter = "CountryRegion = 'USA'"
Me.FilterOn = True

You can set this property by using a table's or form's property sheet (property sheet: A pane that is used to view or modify the properties of various objects such as tables, queries, fields, forms, reports, data access pages, and controls.), a macro (macro: An action or set of actions that you can use to automate tasks.), or VBA code.

You can also set this property in Form view, Report view, Datasheet view, or Layout view, by clicking Filter or Selection under Sort & Filter on the Data tab and clicking one of the commands on the submenu.

 Note   Setting the Filter property has no effect on the ADO Filter property.

You can use the Filter property to save a filter and apply it at a later time. Filters are saved with the objects in which they are created. They are automatically loaded when the object is opened, but they aren't automatically applied.

When a new object is created, it inherits the RecordSource , Filter, OrderBy , and OrderByOn properties of the table or query it was created from.

To apply a saved filter to a form, query, or table, you can click Toggle Filter under Sort & Filter on the Data tab, or use a macro or VBA code to set the FilterOn property to True. For reports, you can apply a filter by setting the FilterOn property to Yes in the report's property sheet.

The Toggle Filter button indicates the state of the Filter and FilterOn properties. The button remains disabled until there is a filter to apply. If an existing filter is currently applied, the Toggle Filter button appears pressed in.

To apply a filter automatically when a form is opened, specify in the OnOpen event property setting of the form either a macro that uses the ApplyFilter action or an event procedure that uses the ApplyFilter method of the DoCmd object.

You can remove a filter by clicking the pressed-in Toggle Filter button, right-clicking the filtered field and clicking Clear filter from..., or using VBA code to set the FilterOn property to False.

Applies to:
Access 2007