Totalling values in Footers

[BradC]

I have a report grouped by PortfolioName then by the StockMarketCode.

In the Footer for StockMarketCode I have a text box [ProfitLoss] with the formula
=IIf([ClosingQuantity]>0 And [SumOfAmount]<0,[SumOfAmount]*-1,(IIf([ClosingQuantity]=0,[SumOfAmount]*-1,0)))

I'd like to be able to take the results in these text boxes and display the total in the Footer of PortfolioName

Can this be done?

I have tried =Sum([ProfitLoss]) but that didn't work

Thanks for the help

 

[Galaxiom]

You can only Sum fields in the recordset. Controls cannot be summed.

BTW, Your formula could be vastly simplified with the Abs() function which returns the magnitude of the argument without the sign.

 

[BradC]

Thanks for that - what a shame

I'm new to Access, so can I push the freindship and ask how I use Abs(<<number>>) Expression in my formula

 

[stopher]

You can apply the total in two ways...

One way is to copy the whole expression into your Sum(). So the full expression in the total box will be:

=SUM(IIf([ClosingQuantity]>0 And [SumOfAmount]<0,[SumOfAmount]*-1,(IIf([ClosingQuantity]=0,[SumOfAmount]*-1,0))))

Another way is to create a new field for ProfitLoss in your underlying query using the formula. Then you have this field available to sum as you please.

hth
Chris

 

[vbaInet]

BTW, Your formula could be vastly simplified with the Abs() function which returns the magnitude of the argument without the sign.

You can only Sum fields in the recordset. Controls cannot be summed.

Another way is to create a new field for ProfitLoss in your underlying query using the formula. Then you have this field available to sum as you please.

Combining the ideas from GalaxiomAtHome and Stopher you get:

ProfitLoss: IIF(Nz([ClosingQuantity], -1) > -1, Abs([SumOfAmount]), Null)

This is what you will put in a new column in the record source of your report.

Now you can sum that field using =Sum([ProfitLoss]) as the control source of the textbox.