Sum IIF Calculation in Reports (1 Viewer)

[Mike A]

I understand Access will not Sum calculated columns in a Report unless the original calculation is repeated in the Sum statement.

One of my calculated columns includes an IIF statement...
What structure would someone suggest to Sum a complex IIF statement?
Everything I have tried comes out 'too complex'!
All suggestions are greatly appreciated!!

 

[Former]

How complicated is the iif ?

 

[Mike A]

I am setting up a Capital Asset database.
There are 3 columns of numbers to Sum.
1. Purchase Cost
2. Accumulated Depreciation and
3. Net Book Value

The last 2 columns are calculated in a Query.

2. Accumulated Depreciation
is calculated by

IIF(([Depreciation/Month] * [# Months] >=
[Purchase Price],[Purchase Price],
([Depreciation/Month * # Months]))

3. Net Book Value
is calculated by subtracting
column #2 from column #1 in the Query.

Any and all help to Sum column 2 and 3 in the Report is greatly appreciated!

 

[Former]

I think that you are trying to calculate all the information on the report itself. Instead do the calculations in a query.

E.g. have a field like

Acc_Dep:IIF(([Depreciation/Month] * [# Months] >=
[Purchase Price],[Purchase Price],
([Depreciation/Month * # Months]))


Then base the report on the query, and you should be able to sum the Acc_Dep without trouble.

 

[Mike A]

Tks for the feedback!
Yes, that is what I have done.

The IIF calculation is in the Query
but I haven't been able to get it to Sum in the Report.

It is my understanding the formula in the Query has to be repeated in the Report formula to get the column to Sum in the Report???

A complex challenge!!

 

[Pat Hartman]

Possible errors:
1. Are you sure you are using Sum() NOT DSum()
2. Is the Sum() in a group or report footer. You can't do a sum this way in a page footer.
3. Are you really summing a column or are you trying to add two columns together. To add two columns the syntax is =fld1 + fld2

 

[Mike A]

To: Former

Tks again for your feedback!
FYI-
See Sept 12th discussion re:
Sum Totals in Reports ?!
by John Bull

Accesswatch.member posted Sept 10th includes a couple of excellent references on
'How to Sum a Calculation in a Report'.

Tks again!

 

[Mike A]

Hi Pat

I have been trying to use the Sum() calculation...

The Report has
a Detail Section
a Branch Section
a Class Section
then Page
and Report Sections

I am trying to Sum each Asset Classification within each Company Branch, Sum by Company Branch and then have a Grand Total at the bottom of the Report.

ie:
Sum (Furniture and Fixture Assets)
Sum (Office Equipment Assets)

Sum (All Assets at Branch Location)

Sum (Grand Total) at bottom of Report

I can Sum ()the column that is straight data input from the Query in the Branch Section, the Class Section and the Report Total Section, but haven't been able to Sum the calculated columns from the Query???!

Am wondering if it is possible to create a 2nd Query from the 1st Query....
will this then hide the calculations if I run the Report from the 2nd Query instead of from the 1st Query with the complex calculations in it???

Hopefully there is an 'easy' solution to this!
Trust this makes sense!
Tks Pat
Mike