locked
Creating totals in Access report RRS feed

  • Question

  • I am developing a report that will have data grouped by month across the top (i.e. Jan, Feb, etc).

    For the detail row, I have each field configured with a formula.  For the month of December for example, the field name is txtDecAUM and the formula is:

    =IIf(Month([CloseDate])=12,[SaleAmount],0)

    This portion works fine and the data displays correctly.  My problem is when I try and total the columns.  No matter what I do, I receive an error.  

    I've tried this:  

    =Sum([txtDecAUM])

    Thinking that perhaps the values are not being stored as numbers, I tried this:

    =Sum(Clng([txtDecAUM]))

    I then tried this instead:

    =Sum(IIf(Month([CloseDate])=12,[SignedAUM],0))

    Nothing seems to work.  How do I total those values?


    Tuesday, December 4, 2012 10:16 AM

Answers

  • Which footer are you using? You can calculate sums in a group footer and in the report footer, but not in the page footer.

    Calculating a sum in the Detail section doesn't seem very useful to me...


    Regards, Hans Vogelaar

    • Marked as answer by JonnyBravoII Wednesday, December 5, 2012 9:20 AM
    Tuesday, December 4, 2012 11:11 AM

All replies

  • You can't sum a control. You can only sum a field from the record source, or an expression based on fields from the record source.

    Since the control source of txtDecAUM is =IIf(Month([CloseDate])=12,[SaleAmount],0), try the following in the control source of the total text box:

    =Sum(IIf(Month([CloseDate])=12,[SaleAmount],0))


    Regards, Hans Vogelaar

    Tuesday, December 4, 2012 10:44 AM
  • I've tried that and I still receive an error.  I thought for sure that would work but it doesn't.

    I've just discovered though that if I place the formula in the detail section and then put a new control in the footer and point to the detail total, it all works fine.  Strange that it will total in the detail section but not the footer.

     
    Tuesday, December 4, 2012 10:54 AM
  • Which footer are you using? You can calculate sums in a group footer and in the report footer, but not in the page footer.

    Calculating a sum in the Detail section doesn't seem very useful to me...


    Regards, Hans Vogelaar

    • Marked as answer by JonnyBravoII Wednesday, December 5, 2012 9:20 AM
    Tuesday, December 4, 2012 11:11 AM