asked on

Access2003 Sum a Calculated Control in a Report

Hello,

This is a continuation of "AC2003 IIF function calculation". The calculated field is located in the detail section of the report and it has the following code: IIF([BidAmount]=0, [BudgetAmount]*[GetPercent], [BidAmount]*[GetPercent]).

What I need to do is total the projected sales for the person, month (everyone), and a grand total. I have footers in the report for the person, month, and the report footer itself for the grand total.

How do I go about getting the sub-totals and grand total to tally from the calculated field in the detail section of the report?

Thank you,
tj

Mike Eghtebas

=Sum(IIF([BidAmount]=0, [BudgetAmount]*[GetPercent], [BidAmount]*[GetPercent]))

Add footer to section you want it to be displayed. Above goes to the control source of the text box you add to that footer.

Mike

dan_vella

You should just be able to sum the calculated field. Say the calculation is in a textBox text1 you should be able to sum this in each group by adding another textbox with =sum([text1]) in it

Mike Eghtebas

Btw, If field BidAmount is not a rwquired field, make sure to use NZ() in it.

Nz([BidAmount],0)=0

tjheroff

ASKER

Using the IIF statement repeatedly does not work because I need the totals for both the bidamount and budgetamount combined for the sales person, monthly subtotal for all sales people, and grand total.

So if a sales person has both bidamounts and budgetamounts for the month, that amount has to be combined and captured. The getpercent determines the projected sale based on either the bidamount or budgetamount.

I did try dan_vella's suggestion and I receive a dialog box asking for the parameter of the text box for each section (footer).

Thank you,
tj

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tjheroff

ASKER

Thank you shanesuebsahakarn, that worked!
tj