ACC2002: Decimal Field Is Truncated When You View an Object in PivotTable and PivotChart View (310264)

MORE INFORMATION

The Currency data type most closely matches the behavior of the Decimal data type, as far as internal storage. Both the Currency and the Decimal data types are scaled integer data types. However, the Currency data type is limited to 4 places to the right of the decimal point whereas the Decimal data type supports up to 28 places to the right of the decimal point. If you must store 4 or fewer digits to the right of the decimal point, use the Currency data type.

The Double and Single data types support decimal storage, but both are floating-point data types. Because decimal fractions do not always have exact binary equivalents, using a floating-point data type can result in rounding errors. Use these data types only if you must store more than 4 digits to the right of the decimal point.

Steps to Reproduce the Behavior

1. Open the sample database Northwind.mdb.
2. Create a new table in Design view.
3. Add the following fields to the table:

      Table: Table1
      ----------------------------
      Field Name: ID
      Data Type: AutoNumber
      Indexed: Yes (No Duplicates)
   
      Field Name: Amount
      Data Type: Number
      Field Size: Decimal
      Precision: 10
      Scale: 4
     
      Table Properties: Table1
      ------------------------
      PrimaryKey: ID

4. Save the table as Table1.
5. On the View menu, click Datasheet View.
6. Add the following records to the table:

   ID	Amount
   1	34223.2345
   2	234325.9234
   3	92452.494
   4	3467.3469

7. On the View menu, click PivotTable View.
8. Drag the Amount field from the PivotTable field list to the Drop Totals or Detail Fields Here area.
   
   Note that all digits after the decimal point have been truncated, and that the data is displayed as an integer value.