You can use the ApplyFilter action to apply a filter, a query, or a SQL WHERE clause to a table, form, or report. This action restricts or sorts the records in the table, or the records from the underlying table or query of the form or report. For reports, you can use this action only in a macro that is specified by the report's OnOpen event property.
Note
You can use this action to apply a SQL WHERE clause only when you apply a server filter. A server filter can't be applied to a stored procedure's record source.
Note
The ApplyFilter macro action isn't available in Access web apps.
Setting
The ApplyFilter action has the following arguments.
| Action argument | Description |
|---|---|
| Filter Name | The name of a filter or query that restricts or sorts the records of the table, form, or report. You can enter the name of an existing query or a filter that has been saved as a query in the Filter Name box in the Action Arguments section of the Macro Builder pane. Note: When you use this action to apply a server filter, the Filter Name argument must be blank. |
| Where Condition | A valid SQL WHERE clause, without the word WHERE, or an expression that restricts the records of the table, form, or report.Note: In a Where Condition argument expression, the left side usually contains a field name from the underlying table or query for the form or report. The right side usually contains the criteria that you want to apply to this field to restrict or sort the records. For example, the criteria can be the name of a control on another form that contains the value that you want the records in the first form to match. The name of the control should be fully qualified, for example, Forms!formname!controlname.Field names should be surrounded by double quotation marks, and string literals should be surrounded by single quotation marks. The maximum length of the Where Condition argument is 255 characters. If you need to enter a longer SQL WHERE clause, use the ApplyFilter method of the DoCmd object in a Visual Basic for Applications (VBA) module. You can enter SQL WHERE clause statements of up to 32,768 characters in VBA. |
Note
You can use the Filter Name argument if you've already defined a filter that provides the appropriate data. You can use the Where Condition argument to enter the restriction criteria directly. If you use both arguments, Access applies the WHERE clause to the results of the filter. You must use one or both arguments.
Remarks
You can apply a filter or query to a form in Form View or Datasheet View.
The filter and WHERE condition that you apply become the setting of the form's or report's Filter or ServerFilter property.
For tables and forms, this action is similar to selecting Apply Filter/Sort or Apply Server Filter on the Records menu. The menu command applies the most recently created filter to the table or form, whereas the ApplyFilter action applies a specified filter or query.
In an Access database, if you point to Filter on the Records menu and then select Advanced Filter/Sort after you run the ApplyFilter action, the Advanced Filter/Sort window shows the filter criteria that you selected with this action.
To remove a filter and display all records for a table or form in an Access database, you can use the ShowAllRecords action or the Remove Filter/Sort command on the Records menu. To remove a filter in an Access project (.adp), return to the Server Filter By Form window and remove all filter criteria. Then select Apply Server Filter on the Records menu on the toolbar, or set the ServerFilterByForm property to False (0).
When you save a table or form, Access saves any filter that is currently defined in that object. However, it won't apply the filter automatically the next time that the object is opened, although it will automatically apply any sort that you applied before the object was saved. If you want to apply a filter automatically when a form is first opened, specify a macro that contains the ApplyFilter action or an event procedure that contains the ApplyFilter method of the DoCmd object as the OnOpen event property setting of the form. You can also apply a filter by using the OpenForm or OpenReport action, or their corresponding methods. To apply a filter automatically when a table is first opened, open the table by using a macro that contains the OpenTable action, followed immediately by the ApplyFilter action.
Example
Apply a filter by using a macro
The following macro contains a set of actions. Each action filters the records for a Customer Phone List form. It shows how to use the ApplyFilter, ShowAllRecords, and GoToControl actions. It also shows how to use conditions to determine which toggle button in an option group has been selected on the form. Each action row is associated with a toggle button that selects the set of records that start with A, B, C, and so on, or all records. Attach this macro to the AfterUpdate event of the CompanyNameFilter option group.
| Condition | Action | Arguments: Setting | Comment |
|---|---|---|---|
| [CompanyNameFilters]=1 | ApplyFilter | Where Condition: [CompanyName] Like "[AÀÁÂÃÄ]*" | Filter for company names that start with A, À, Á, Â, Ã, or Ä. |
| [CompanyNameFilters]=2 | ApplyFilter | Where Condition: [CompanyName] Like "B*" | Filter for company names that start with B. |
| [CompanyNameFilters]=3 | ApplyFilter | Where Condition: [CompanyName] Like "[CÇ]*" | Filter for company names that start with C or Ç. |
| ... Action rows for D through Y have the same format as A through C ... | |||
| [CompanyNameFilters]=26 | ApplyFilter | Where Condition: [CompanyName] Like "[ZÆØÅ]*" | Filter for company names that start with Z, Æ, Ø, or Å. |
| [CompanyNameFilters]=27 | ShowAllRecords | Show all records. | |
| [RecordsetClone].[RecordCount]>0 | GoToControl | Control Name: CompanyName | If records are returned for the selected letter, move focus to the CompanyName control. |