ACC2002: Date Grouping Not Preserved and Year Value Not Displayed Correctly in PivotTable and PivotChart Views (295220)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q295220
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

You insert a date field as a column in PivotTable view, and then apply grouping to the date field. When you switch to PivotChart view and click the drop-down list of the date field, the four-digit year is displayed incorrectly. For example, the year 2000 appears as 1900. When you switch to PivotTable view, the date column header is formatted the same way the date field is formatted in the table design. The four-digit year is displayed incorrectly.

RESOLUTION

If you want to group the PivotTable by years, expand the auto-created date hierarchy tree for the date field in the PivotTable field list, and then drag Years to the Column Fields area of the PivotTable. If you want to group the PivotTable by months, you can temporarily set the format of the date field in table Design view to mmm.

If you drag Months from the auto-created date hierarchy tree to the Column Fields area and view the results, the data is grouped first by year, and then by months. The auto-created date hierarchies do not produce the same results as grouping a date field.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the Orders table in the sample database Northwind.mdb.
  2. On the View menu, click PivotTable View.
  3. From the PivotTable field list, drag Order Date to the Column Fields area.
  4. Click the Order Date box in the Pivot Table.
  5. On the View menu, click Properties.
  6. On the Filter and Group tab, group items by Months, and then close the property sheet.
  7. From the PivotTable field list, drag Order ID to the Detail Fields area.
  8. Click the Order ID column header row.
  9. Click the AutoCalc button on the toolbar, and then click Count. Note the totals at the bottom of each column.
  10. On the View menu, click PivotChart View.
  11. Click the Order Date box. Note that the year appears as 1901 instead of 2001.
  12. On the View menu, click PivotTable View.
Note that the Order Date column is formatted as DD-MMM-YYYY, and the year appears as 1901 instead of 2001.
Modification Type:MajorLast Reviewed:9/26/2003
Keywords:kbbug kbnofix KB295220
©2003 Microsoft Corporation. All rights reserved.