Access2003 Sum a Calculated Control in a Report


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,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
=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.

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 EghtebasDatabase and Application DeveloperCommented:
Btw, If field BidAmount is not a rwquired field, make sure to use NZ() in it.

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tjheroffAuthor Commented:
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,

Why not just include the field in the report's underlying query, i.e.:

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

You can then place textboxes in the various footers that have a control source of:

which will then sum the value at the appropriate level.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tjheroffAuthor Commented:
Thank you shanesuebsahakarn, that worked!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.