empty calculated group level field when exporting to excel

Question

I have created a report. The report has 2 group levels and a report footer.

In each of these levels I have 2 sum fields:

• SumBrutto: Sum([Brutto])
• SumNetto: Sum([Netto])

I calculate the percentage of SumNetto / SumBrutto * 100. It's important that this calculation is done on each level separately because it is required this way. So I can't see a way to bring this calculation to the query and use sum and average at the query level.

In the report everything looks great. But if I export the report to excel, the calculated fields on each group level and the report footer are empty.

For exporting, I use the following command

DoCmd.OutputTo acOutputReport, "r_Report", acFormatXLS, "C:\Temp\Report.xls", True

Any other format that make sense like acFormatXLSX, acSpreadSheetExcel.. brings an error "format not available"

So what can I do to export the calculated fields into excel

Answer 1

Hi egbes,

I try to make a test on my side and add some calculated fields in report.

I use the same syntax that you had used.

Sub demo()
DoCmd.OutputTo acOutputReport, "stud", acFormatXLS, "C:\Users\v-padee\Desktop\Report1.xls", True
End Sub

below is the output:

Highlighted values are calculated fields.

but calculated values in the footer are not displaying.

so try to put that calculated fields in header and it is displaying when I open the excel file.

Reference:

DoCmd.OutputTo Method (Access)

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi Deepak,

thank you for your investigation and your input to my question. At least this is a workaround with low effort for me, but it is an acquired taste to have the summaries at the top. With changing the point of view it may be selled as a feature.

Thank you

egbes