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

ID: Q154396

The information in this article applies to:

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


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


  2. In the report footer, add a text box with the following properties: 
    
      Text box:
         Name: test
         ControlSource: =sum([ShipperID])


  3. Click "Analyze it With MS Excel" in the Office Links box on the toolbar.


  4. 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          : kbinterop IntpOff 
Version           : 7.0
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 30, 1999