Summing SubTotals in a report

[rclaxton]

This is my first post on this forum. My question is regarding report footer subtotals. The report is grouped on a RefTag number, and ObjCode number. In an ObjCode footer are calculated two expenditure amounts/columns for a DebitSub (positive values for the objcode detail totals) and CreditSub (negative values for the objcode detail totals), so that if the total value for all ObjCode(s) is positive it displays in the Debit column, negative in the Credit column. The control source I am using for each is:
DebitSub >>: =IIf([AmtExpend]>0,(Sum([AmtExpend])),0)
CreditSub >>: =IIf([AmtExpend]<0,(Sum([AmtExpend])),0)

How do I get a total for both subtotals? I’ve tried the =Sum([DebitSub]) in another textbox, but it doesn’t pull the DebitSub value from the report, instead it prompts for a value, which won’t work.

The report should look something like:
RefTagHeader:
RefTag#
Date

ObjCodeFooter:
NAME OBJCODE DEBIT CREDIT
CCenter 411601 $0.00 ($43.56)
CCenter 414078 $745.96 $0.00
CCenter 415123 $0.00 ($1,116.01)

RefTagFooter:
DEBIT TOTAL CREDIT TOTAL
$$$$$$$$$$ $$$$$$$$$$$
GRAND TOTAL = ($413.61)

It seems simple enough but I just can't seem to figure it out. Please help.

 

[traingamer]

Wouldn't it just be

=Sum([AmtExpend])

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rclaxton]

That produces the Grand Total for AmtExpend. I need separate totals for the positive value Debit and negitive Credit Totals.

I should mention that each Subtotal is the sum of the AmtExpend for the ObjCode detail and as mentioned, positive values appear in the Debit column and negative values appear in the Credit column of the ObjCode Footer.

 

[traingamer]

Sorry. You just use the same formula as before. You can't sum a calculated field as you were trying to do.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rclaxton]

I'm not sure how this helps. Can you offer any suggestions? Can I provide any additional information that might you, help me find a way to sum up this situation?

 

[CosmoKramer]

A similar statement like this one should work in the report footer:

=Sum(IIf([AmtExpend]>0,[AmtExpend],0))

I'm CosmoKramer, and I approve this message.

 

[rclaxton]

Thanks to all for their responces, past, present and future.
I tried that. It does produce a proper positive and/or negative result but it's not exactly what I need. The problem is that there may be mutilpul Objcode detail values with varing +/- values which don't show on the report like:

OBJCODE DEBIT CREDIT
411601 $863.01 $0.00
411601 $0.00 ($117.05)

The Report (ObjCode Footer) shows:
411601 $745.96 $0.00

The subtotal, and the only value that is reported in the "Debit" column of the ObjCode footer for 411601 is $745.96. My problem is I need to capture and sum each of those subtotal values as a Debit Report total for that Report in another footer. Is it possible? It was done, by someone else, in an old RBase database that I'm converting and this is the last item I need to complete the conversion.

Sorry if I sound a little desparate, I've been beating myself up over this one for a while now.

Thanks again to everyone, you guys are a great group.

 

[rclaxton]

Thanks anyway. Still looking.......

 

[Gupi]

The solution to a very similar problem I used was to get a form to do the calculation and then get the report to read the credit and debit totals off the relevant text boxes on the form.

 

[rclaxton]

I figured it out. Access wouldn't let me perform a calculation on a calculated field the way I need. So what I did was to create a query based on my orginal report query to perform the calculations and then develope another report based on my calculated query, which I inserted as a subreport to the original. I'm sure there is a simple solution but this works and its finished, for now.

Thanks to all who offered suggestion and this form for allowing us to participate.