Adding up Calculated Fields in Reports

[NoviceAccessUser-Melanie]

How do I set up a report where I need to add the values from 2 fields in the
same report? Each of the two fields are calculated fields. For example:
CalculatedActual:
=Sum((IIf([Ind]="Actual-LIHTC",[1],"0"))+(IIf([Ind]="Actual-HTC",[1],"0")))

and

CalculatedOriginal:
=Sum((IIf([Ind]="Original-LIHTC",[1],"0"))+(IIf([Ind]="Original-HTC",[1],"0")))

to come up with Field called Difference which is [CalculatedActual] -
[CalculatedOriginal].

I really appreciate your help.

 

[Jeff Boyce]

As I recall, Access doesn't undertand the "calculated" fields you've
created.

To get a total of two calculated fields, I believe you'll need to reference
the underlying raw data and "do the math" at that level.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"NoviceAccessUser-Melanie"

 

[fredg]

How do I set up a report where I need to add the values from 2 fields in the
same report? Each of the two fields are calculated fields. For example:
CalculatedActual:
=Sum((IIf([Ind]="Actual-LIHTC",[1],"0"))+(IIf([Ind]="Actual-HTC",[1],"0")))

and

CalculatedOriginal:
=Sum((IIf([Ind]="Original-LIHTC",[1],"0"))+(IIf([Ind]="Original-HTC",[1],"0")))

to come up with Field called Difference which is [CalculatedActual] -
[CalculatedOriginal].

I really appreciate your help.

You should repeat the calculation, subtracting the second calc. from
the first.

Difference =Sum((IIf([Ind] = "Actual .... etc))) - Sum((IIf([Ind] =
"Original .... etc.)))

Note: Don't place the quotes around the Zeros, i.e. ,"0")).
You are adding a 0 to the sum so it should be a Number value 0, not a
Text 0.

 

[NoviceAccessUser-Melanie]

Hi, I have never created complex reports in Access and am just learning to
produce reports required by my department. Which is why I don't know all the
rules on what Access understands or not.

But the 2nd part of your reply made me look at my report field again and was
able to come up with the answer with one lengthy expression.

=Sum(((IIf([Ind]="Actual-LIHTC",[1],"0"))+(IIf([Ind]="Actual-HTC",[1],"0")))-(IIf([Ind]="Original
Proforma-LIHTC",[1],"0")+(IIf([Ind]="Original Proforma-HTC",[1],"0"))))
Thanks for your help.

As I recall, Access doesn't undertand the "calculated" fields you've
created.

To get a total of two calculated fields, I believe you'll need to reference
the underlying raw data and "do the math" at that level.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"NoviceAccessUser-Melanie"

How do I set up a report where I need to add the values from 2 fields in
the
same report? Each of the two fields are calculated fields. For example:
CalculatedActual:
=Sum((IIf([Ind]="Actual-LIHTC",[1],"0"))+(IIf([Ind]="Actual-HTC",[1],"0")))

and

CalculatedOriginal:
=Sum((IIf([Ind]="Original-LIHTC",[1],"0"))+(IIf([Ind]="Original-HTC",[1],"0")))

to come up with Field called Difference which is [CalculatedActual] -
[CalculatedOriginal].

I really appreciate your help.

 

[NoviceAccessUser-Melanie]

Thanks that worked. But what should I do if I don't want to show the zeros in
the field (for the part of the report for future years and should really be
blank/not display anything). Can I just do "" (quotes)?

How do I set up a report where I need to add the values from 2 fields in the
same report? Each of the two fields are calculated fields. For example:
CalculatedActual:
=Sum((IIf([Ind]="Actual-LIHTC",[1],"0"))+(IIf([Ind]="Actual-HTC",[1],"0")))

and

CalculatedOriginal:
=Sum((IIf([Ind]="Original-LIHTC",[1],"0"))+(IIf([Ind]="Original-HTC",[1],"0")))

to come up with Field called Difference which is [CalculatedActual] -
[CalculatedOriginal].

I really appreciate your help.

You should repeat the calculation, subtracting the second calc. from
the first.

Difference =Sum((IIf([Ind] = "Actual .... etc))) - Sum((IIf([Ind] =
"Original .... etc.)))

Note: Don't place the quotes around the Zeros, i.e. ,"0")).
You are adding a 0 to the sum so it should be a Number value 0, not a
Text 0.

 

[fredg]

Thanks that worked. But what should I do if I don't want to show the zeros in
the field (for the part of the report for future years and should really be
blank/not display anything). Can I just do "" (quotes)?

How do I set up a report where I need to add the values from 2 fields in the
same report? Each of the two fields are calculated fields. For example:
CalculatedActual:
=Sum((IIf([Ind]="Actual-LIHTC",[1],"0"))+(IIf([Ind]="Actual-HTC",[1],"0")))

and

CalculatedOriginal:
=Sum((IIf([Ind]="Original-LIHTC",[1],"0"))+(IIf([Ind]="Original-HTC",[1],"0")))

to come up with Field called Difference which is [CalculatedActual] -
[CalculatedOriginal].

I really appreciate your help.

You should repeat the calculation, subtracting the second calc. from
the first.

Difference =Sum((IIf([Ind] = "Actual .... etc))) - Sum((IIf([Ind] =
"Original .... etc.)))

Note: Don't place the quotes around the Zeros, i.e. ,"0")).
You are adding a 0 to the sum so it should be a Number value 0, not a
Text 0.

You code does NOT show zero's in the field. It adds a 0 to the Sum if
the criteria is not met. That's what it's supposed to do.

To not display the 0, set the Format property of that control (in the
report) to:
#;-#;

Only values greater than or less than 0 will display.

Look up Format property + Number and Currency datatypes
in ACCess Help.