For forms and reports, there are a number of ways that you can filter records at the server before they are retrieved from the database. By filtering records at the server, you can improve the form or report's performance. Besides basing the form or report on a query that includes criteria (search conditions) to limit the records, you can:
Define a specific filter in the ServerFilter property of the form or report. When you or others open the form or report, it contains only the records that meet the criteria that you specified in the ServerFilter property. You can't change the filter unless you change the property or use the filtering techniques described in the remainder of this help topic to limit the records once they are local.
Prompt for a different filter each time you open a form in Form view or Datasheet view. You do this by setting the form's ServerFilterByForm property to Yes. Then, when you switch to Form view or Datasheet view, Microsoft Access displays the Server Filter By Form window, which is a copy of the form (or datasheet) that you are filtering, but without records. In the form, you can specify different criteria by choosing your search values from drop-down lists in each field. When you apply the filter, Microsoft Access sets the ServerFilter property to the filter you defined in the Server Filter By Form window.
Note You can't use the Server Filter By Form window with a form bound to a stored procedure. Instead, consider using parameters in the stored procedure, and then setting the form's InputParameters property to bind the parameters to the form.
Ways to filter records after they are retrieved from the database
For forms and datasheets, you can filter records after they've already been retrieved. This is true even for forms that were initially filtered at the server. Once the records are displayed, you can use these local filtering techniques to limit the records:
Filter By Form displays a copy of the form (or datasheet) that you are filtering, but without records. You can specify different criteria each time you use the form or datasheet by choosing your search values from drop-downlists in each field or by specifying an expression. There's no need to scroll through all the records in a form or datasheet, and you can specify multiple criteria at once.
Filter By Selection finds records with a field containing all or part of a value you selected in that same field on a form, subform, or datasheet. If you want to further limit the records, you can continue selecting other values or partial values. Each time you apply the filter, it uses the previous values that you selected and any subsequent values to determine the filtered set of records.
Filter For Input displays an area on the shortcut menu where you can type the exact value that you're searching for in the current field or the expression whose result you want to use as your criteria.
Ways to filter records for a data access page
Ways to filter records before they are retrieved from the database
Filtering records before they are retrieved from the database can improve data access page performance because there are fewer records to download from the underlying database.
-
You can base your page on a query that includes criteria (search conditions) to limit the records.
-
In a Microsoft Access database you can define a server-side filter in the ServerFilter property of the RecordsetDef object, or by using the Filter property in Visual Basic for Applications (VBA) or Microsoft Visual Basic Scripting Edition (VBScript). In a Microsoft Access project you can define a server-side filter in the ServerFilter property in VBA or VBScript.
-
On a grouped data access page, you can create a filter that retrieves data for a specific group, such as orders for a specific customer. The data access page won't display any records until you choose the group from a drop-down list in Page view or Microsoft Internet Explorer. Once you choose the group, the filter is applied and only those records are retrieved.
On an ungrouped data access page, you can create a filter by designating a field as the filter field. When you choose a value from the filter field, all records that contain the selected value are retrieved.
-
In Design view, you can add a server-side filter to a hyperlink on a page. Then, when you click the link in Page view or in the browser, only the records specified in the filter are displayed on the page you jump to.
Ways to filter records after they are retrieved from the database
About filtering data in a PivotTable or PivotChart view
About filtering data in a PivotTable view
You can use the filter feature to find specific data values or all data that matches a value.
Filtering a field (Autofiltering) You can filter fields in the row, column, and detail areas. When you filter a field, you select one or more items of data in the field that you want to view, and hide the other items.
First, the Sport field is filtered to display only Golf sales ...
... and then the Quarter field is filtered to display only Golf sales in Qtr3.
When you filter a field, the drop-down arrow 
for the filtered field changes to blue instead of black, and the AutoFilter button 
on the toolbar is selected.
Filtering a field is particularly helpful when you have a large amount of source data but you want to focus on specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus.
Filtering based on the data in one cell (Filter by Selection) You can filter a field to display only data that matches the value in a selected cell. Filtering by selection is particularly useful for fields in the detail area, when you want to view all of the rows that contain a particular value.
Using a field in the Filter axis You can also filter data by using filter fields. When you select one or more items in the filter field, the data that's displayed and calculated in the entire PivotTable view changes to reflect those items. You can add new filter fields or move existing fields to the filter area.
Filter field
Conditional filtering You can apply a filter to a row or column field to show the top or bottom n items based on a total. For example, you can filter for the three cities that generated the most sales or the five products that are least profitable. Instead of n items, you can also choose to filter for a certain percentage of items. For example, you can filter for the top or bottom 25% of salespeople based on the number of orders handled. If there are 40 salespeople, after the filter is applied, you will see data for 10 people.
-
Filters are additive. Each filter you apply is added to the ones you already have in effect. However, when you apply a filter by selection, the conditional filter already applied on the field is removed before the filter by selection is applied.
-
Conditional filters are independent of each other. The result of one conditional filter does not affect the result of another.
-
An autofilter is always applied before a conditional filter, regardless of the order in which you applied them. For example, if you set a conditional filter to show the top two cities based on sales, followed by an autofilter on the ShippedCity field to include only five cities, the PivotTable view will show the top two of the five cities you selected.
Removing and reapplying a filter
You can display all data for all fields by turning off autofiltering. However, the filter settings are retained, so that when you turn autofiltering back on, the data that was previously displayed or hidden is again displayed or hidden.
Filter settings are retained when you move fields to change the layout. Also, if you remove a field and later add the field back to the layout, the same items are again hidden.
If you don't want to retain your filter selections, make sure the AutoFilter button is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.
Identifying filters that are currently in effect
-
When items in a field are hidden by autofiltering or filter by selection, the drop-down arrow
in the field label is blue. Click the blue arrow 
to see the filter that is in effect. -
When items in a field are hidden by conditional filtering, a funnel
appears to the left of the drop-down arrow . You can get more information about the current filter in the Properties dialog box.
Effects of filtering on calculations
Totals can include only the displayed data, or both visible and hidden data. The default is to exclude hidden data from totals. You can change the setting to include or exclude hidden data in totals. This setting affects only data that's hidden by filtering row and column fields; data that's hidden by using filter fields is always excluded from totals.
About filtering data in a PivotChart view
You can use the filter feature to find specific data values or all data that matches a value.
Filtering a field (Autofiltering) You can filter fields in the category, series, and filter areas. When you filter a field, you select one or more items of data in the field that you want to view, and hide the other items.
Filtering a field is particularly helpful when you have a large amount of source data but you want to focus on specific areas. When you filter a field, you can display the data for a single item, or you can select some items to display and other items to hide. You can filter on more than one field at a time to further narrow the focus.
Category field Region showing all items (not filtered)
Category field Region filtered to show South and West region items
Conditional filtering You can apply a filter to a series or category field to show the top or bottom n items based on a total. For example, you can filter for the three cities that generated the most sales or the five products that are least profitable. Instead of n items, you can also choose to filter for a certain percentage of items. For example, you can filter for the top 25% or bottom 25% of salespeople based on the number of orders handled. If there are 40 salespeople, after the filter is applied, you will see data for 10 people.
Filters are additive. Each filter you apply is added to the ones you already have in effect.
Removing and reapplying a filter
You can display all data for all fields by turning off autofiltering. However, the filter settings are retained, so that when you turn autofiltering back on, the same data that was previously displayed or hidden is again displayed or hidden.
Filter settings are retained when you move fields to change the layout. Also, if you remove a field and later add the field back to the layout, the same items are again hidden.
If you don't want to retain your filter selections, make sure the AutoFilter button is not selected before you start selecting items to filter. If the button is not selected, selecting new items to filter automatically turns filtering on and removes your former filter settings.
Identifying the filters that are currently in effect
When items in a field are hidden by autofiltering, the drop-down arrow in the field label is blue. Click the blue arrow to see the filter that is in effect.