ACC2002: Filter By Form Command Applied to Datasheet View Does Not Affect PivotTable or PivotChart View (287469)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q287469
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you apply a filter to a table in a Microsoft Access project and switch to PivotTable or PivotChart view, the complete set of records is represented in that view instead of the filtered set.

CAUSE

This behavior occurs because the client-side filters applied to tables in an Access project do not affect the selection of records in a table's PivotTable or PivotChart view.

RESOLUTION

If you want to filter the records for PivotTable or PivotChart view in an Access project, you must apply a server filter, which can only be done by using a form or a report. To do this, follow these steps:
  1. Open the sample project NorthwindCS.adp.
  2. In the Database window, click Forms under Objects, and then click New. In the New Form dialog box, click AutoForm: Datasheet, select the Customers table, and then click OK.
  3. On the View menu, click Design View, and then again on the View menu, click Properties.
  4. On the Property sheet's Data tab, type CustomerID Like 'a%' in the ServerFilter property.
  5. On the View menu, click PivotTable View or PivotChart View.
  6. From the PivotTable or PivotChart Field list, drag CustomerID to the appropriate section.
Note that only the filtered records are represented.

MORE INFORMATION

In an Access database, when you use the Filter By Form command to filter a table's records and then switch to PivotTable view, the records displayed in the PivotTable are the filtered set. This is not the case in an Access project, where the all of the records in the table are displayed in the PivotTable when a client-side filter is applied.

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

Steps to Reproduce the Problem

  1. Open the sample project NorthwindCS.adp.
  2. In the Database window, click Tables under Objects, and then double-click Customers.
  3. On the toolbar, click the Filter By Form button.
  4. In the CustomerID column, type Like 'a%'.
  5. On the toolbar, click Apply Filter and note that the records are filtered correctly.
  6. On the View menu, click PivotTable View.
  7. From the PivotTable Field list, drag CustomerID to the PivotTable's Drop Row Fields Here section.
Note that all records are represented, indicating that the filter has not persisted in PivotTable view.

REFERENCES

For more information about the ServerFilter property, click Microsoft Access Help on the Help menu, type serverfilter in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbprb KB287469
©2003 Microsoft Corporation. All rights reserved.