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

• Microsoft Access 7.0

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 

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