Calculating a Calculated control in a Report 3

[khwaja]

I have a report that has two fields, proposed capex and the other approved capex. In my report I have summed this to give me total capex. I needed to have a group total for both columns as well as the calculated 'Total' column. I could sum two fields at group level without any problem but I could not sum the previously calculated 'Total' field. I tried using the Total text box name 'TTotal' to sum the total column but to no avail. I managed to get around this by using the following formula but I don't suppose this is the right way.

=Sum(IIf(NZ([PropCapex])+NZ([APpCapex])=0,Null,NZ([PropCapex])+NZ([APpCapex])))

Could someone shed light on how to refer to calculated controls when you want to calculate these again.

Regards

 

[paron]

I have usually added the 'record total'(Proposed + Approved) to the query upon which the report is based. That way it's available to the report as a field in its own right, shows up on the field list and all, and it's a simple matter of grouping on it and totaling for the "Grand Total of all totals."

Or have I completely misunderstood the question?

Ron

 

[khwaja]

Thank you for highlighting this dimension. I will go with this for now. But I would still like to have an answer as to whether you can perform any further calculation on controls which are result of prior calculation.

Regards

 

[paron]

I just went and tried it on a report and it worked fine. I use the "Expression Builder" to pick the control name from the list on the report so that I don't get mixed up with field names, other forms, etc., or have speeling erors.

I think your problems may be due to the "special treatment" that calculations in headers and footers receive. For example, I have a report that has 3 levels of totals: for a job, for jobs by a person, and for all jobs by all persons in a department. They all have the same control source: " = Sum(PayHours)." They just show up in different group headers/footers, so what "Sum" means is different for each one.

Now, that being said, I still was able to use the calculated controls in further calculations, as long as I referred to the control name, not the field name. Since the Access wizards default to setting the control's name to the same name as the field that is its "Control Source", it's very easy to mix them up.

In short -- watch out for calculated controls in group headers and footers; what "Sum" refers to varies with where it is on the report.
-- watch out for control names vs. field names; they are often, but not always, the same.

HTH
Ron

 

[JimHorton]

khwaja,
You can't perform vertical/aggregate calculations (ie. sum(), avg() max(), etc) on controls themselves. As Ron pointed out, watch out for the naming issue. A good idea, if you use the wizard to create the report, is to run a for/each loop over all the textboxes to prefix them with "txt". This avoids any confusion or contention--access makes an assumtion, if the source and Control are named the same, as to which one it chooses (I forgot which takes priority since I always prefix with 'txt').

If you need you can go further and prefix group-level controls with, ie, "txtCusFtr", say for Customer Footer, so you'd have the sales total in the detail be "txtCusFtrSales" with a ControlSource of Sum([Sales]). Up in the detail, there'd be a field "txtSales", with source [Sales].

This can help in horizontal caclulations, if performance is an issue. For example, in the Customer Footer:
"txtCusFtrSales" + "txtCusFtrTax" - "txtCusFtrReturns"
..will calculate faster than:
sum(sales) + sum(tax) - sum(Returns)
...since we've already done the Sum()'s--why do them twice? If this horizontal calc can be done in the query, which in this case it can, then all the better--you'd just do Sum([Field in Query that adds sales & tax less returns])

So, I will second Ron's point about the query--you should do as much of the pre-calculations as possible in the query, but avoid doing any sorting or grouping in the query--you're best leaving that to the report. ie if you have a query sorted by ID, and then sort the report by ID, it will sort twice--the Report engine creates it's own internal Aggregate (Group By) query behind the scenes, so avoid double work.

--Jim

 

[paron]

Thanks, Jim, that could have been misleading!

Ron

 

[khwaja]

Thank you all for the tremendous support. I thank you all are very clear in your treatment of the issue.


Cheers