[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access2003 Sum a Calculated Control in a Report

Posted on 2004-11-10
Medium Priority
Last Modified: 2008-03-06

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,
Question by:tjheroff
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12548979
=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.


Expert Comment

ID: 12548989
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
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 12549012
Btw, If field BidAmount is not a rwquired field, make sure to use NZ() in it.

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 12549287
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,

LVL 41

Accepted Solution

shanesuebsahakarn earned 2000 total points
ID: 12549940
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.

Author Comment

ID: 12550519
Thank you shanesuebsahakarn, that worked!

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question