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

x
Solved

# Access2003 Sum a Calculated Control in a Report

Posted on 2004-11-10
Medium Priority
413 Views
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
0
Question by:tjheroff

LVL 34

Expert Comment

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.

Mike
0

LVL 4

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
0

LVL 34

Expert Comment

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

Nz([BidAmount],0)=0
0

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,
tj

0

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:
=Sum([MyField])

which will then sum the value at the appropriate level.
0

Author Comment

ID: 12550519
Thank you shanesuebsahakarn, that worked!
tj
0

## Featured Post

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
Course of the Month19 days, 1 hour left to enroll