Exporting a report to Excel

[DGBagby]

I hope someone can explain why and what I need to do. I have an Access report that my users want exported to an excel spreadsheet.

The report contains the following structure:

Report Header

Detail Header
---------
Detail
Detail
Detail
---------
Detail Footer with Totals

Detail Header
---------
Detail
Detail
---------
Detail Footer with Totals

The detail totals for each section have eight columns with totals, all formals:
Qty Mailed: =Sum([Qty Mailed])
Qty Ordered: =Sum([Qty Ordered])
IncPc: =Sum([DIncome])/Sum([Qty Mailed])
CostPc: =Sum([DCosta])/Sum([Qty Mailed])
Net/Pc: =Sum([Dincome]-[DCosta])/[QtyMail]
LCost/Pc: =Sum([TLCost/Pc])/Sum([Qty Mailed])
Avg Gift: =Sum([DIncome])/Sum([Donors])
& Resp: =(Sum([Donors])/Sum([Qty Mailed]))*100

The report on paper works perfectly. If I try to export the report to excel, it opens in excel formatted correctly, however, totals for each detail section only show in the first two columns. The remaining six columns are empty on the excel sheet. All eight are formulas and I do understand that only the result will show, which is exactly what I want.

I tried everything I can thing of including starting a new database with just data and a new, simpler version, of the the report. I still only get totals results in the first two columns.

This is work data so I cannot share it but if you need more info please ask and I'll see what I can come up with.
I need to know:
1) what am I missing or
2) Is access unable to provide results for more than two columns.

[June7]

You could provide a copy of the db with dummy data.

Access reports often do not export nicely to Excel, especially if there is grouping.

Unless something has changed with 2013, Detail does not have header and footer sections, there is only Detail. So what do you mean by Detail Header/Footer?

[DGBagby]

Yes I can. I have a sample ready to go. How do I get it to you? I don't see how to attach files.

[June7]

Instructions at bottom of my post.

[DGBagby]

I have attached the database sample and a sample of the excel export.

Thank you!

[June7]

Interesting, the export can't handle the percentage calculations. These expressions refer to fields in the RecordSource but are not on the report. What I tried:

1. bind textboxes to fields DIncome, DCosta, TLCost/Pc, Donors in Detail section

2. textboxes in List footer for Sum calcs on those 4 fields

3. change expressions in percentage calcs to reference textbox names

Unfortunately, still does not work. Sorry, apparently the arithmetic calcs cannot export.

I then tried changing the percentage calcs to an Avg() aggregate, like: Avg([Inc/Pc]). Those calcs do export.


Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be Cost_Pct, PctResp.

[DGBagby]

Thank you so much! Even though using Avg doesn't give me the correct result, you did clear up why excel isn't accepting the formulas.

You were of great assistance. If only I knew enough to make custom functions.

Again, thanks! You are appreciated!