Knowledge Base

ACC95: Sum() Function Returns 0 in Report Output to Microsoft Excel

Article ID: 154396

Article Last Modified on 11/17/2000

Microsoft Access 95 Standard Edition

This article was previously published under Q154396

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you output a Microsoft Access report that uses the Sum() function to Microsoft Excel, the result of the calculation in the Microsoft Excel spreadsheet is zero (0).

RESOLUTION

To work around the problem, change the following properties of the bound control being summed (not the text box containing the Sum() function) to the following settings:

   Format: Fixed
   Decimal Places: Desired number of decimal places

NOTE: The report must be analyzed again for the changes to take effect.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 7.0. This problem no longer occurs in Microsoft Access 97.

MORE INFORMATION

Steps to Reproduce Behavior

Open the sample database Northwind.mdb and use the Report Wizard to create an Auto Tabular report based on the Shippers table.

In the report footer, add a text box with the following properties:

      Text box:
         Name: test
         ControlSource: =sum([ShipperID])

Click "Analyze it With MS Excel" in the Office Links box on the toolbar.
When the Microsoft Excel document opens, note that the last value in the ShipperID column (corresponding to the sum function) is 0.

REFERENCES

For more information about using the Sum() function, search for "Sum function," and then "Computing a total for a calculated control on a form or report" using the Microsoft Access for Windows 95 Help Index.

Keywords: kbbug kbfix kbinterop KB154396