none
empty calculated group level field when exporting to excel RRS feed

  • 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

    Thursday, April 20, 2017 3:48 PM

Answers

  • 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 MSDNFSF@microsoft.com.


    Friday, April 21, 2017 1:53 AM
    Moderator

All replies

  • 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 MSDNFSF@microsoft.com.


    Friday, April 21, 2017 1:53 AM
    Moderator
  • 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

    Monday, April 24, 2017 7:44 AM