Calculating in a Report

  • Thread starter Thread starter Avid Reader
  • Start date Start date
A

Avid Reader

Hi all, I have a report that I have set up that I can't seem to get this one
particular column to tally. Here's what I have:


Reb/Set Rebate Potential
Rebate Earned
Name Text46 Rebate 1
Rebate 4
Control Source =15 =15*[Woods]
=IIf([Woods]<[2004 Goals Drivers],0,15*[Woods])

Name Text45 Rebate 2
Rebate 5
Control Source =30 =30*[Irons]
=IIf([Irons]<[2004 Goals Irons],0,30*[Irons])

Name Text47 Rebate 3
Rebate 6
Control Source =30 =30*[Compri]
=IIf([Compri]<[2004 Goals Compri],0,30*[Compri])
--Acct
Footer----------------------------------------------------------------------
------------------------------------------------

If you notice all of the "Rebate" fields are/were text fields they are not
in my original fields list. I have an account footer that sums the values
for each account:

Total Rebate Potential =

Name Text61
Name Earned
Control Source =Sum(15*[Woods]+30*[Irons]+30*[Compri])
Control Source =[Rebate 4]+[Rebate 5]+[Rebate 6]

As far as summing up Rebate 1 thru Rebate 6 I don't have a problem doing it
for Individual accounts but the problem arises when I try to sum the values
on the report footer. On the footer I can calculate Rebates 1 - 3 by
putting:

=Sum(15*[Woods]+30*[Irons]+30*[Compri]) at the footer but I all I get is a
Zero when I try to put in =[Rebate 4]+[Rebate 5]+[Rebate 6]. If I try to
type in Sum([Rebate 4] + [Rebate 5] + [Rebate 6]) when the report runs it
comes up asking for Rebates 4 - 6. Like I said before the "Rebates" are not
in the original field list and I suspect that to be the problem but
unfortunately I don't know how to solve it.

Any help will be greatly appreciated.

Thanks in advance.
 
Avid said:
Hi all, I have a report that I have set up that I can't seem to get this one
particular column to tally. Here's what I have:


Reb/Set Rebate Potential
Rebate Earned
Name Text46 Rebate 1
Rebate 4
Control Source =15 =15*[Woods]
=IIf([Woods]<[2004 Goals Drivers],0,15*[Woods])

Name Text45 Rebate 2
Rebate 5
Control Source =30 =30*[Irons]
=IIf([Irons]<[2004 Goals Irons],0,30*[Irons])

Name Text47 Rebate 3
Rebate 6
Control Source =30 =30*[Compri]
=IIf([Compri]<[2004 Goals Compri],0,30*[Compri])
--Acct
Footer----------------------------------------------------------------------
------------------------------------------------

If you notice all of the "Rebate" fields are/were text fields they are not
in my original fields list. I have an account footer that sums the values
for each account:

Total Rebate Potential =

Name Text61
Name Earned
Control Source =Sum(15*[Woods]+30*[Irons]+30*[Compri])
Control Source =[Rebate 4]+[Rebate 5]+[Rebate 6]

As far as summing up Rebate 1 thru Rebate 6 I don't have a problem doing it
for Individual accounts but the problem arises when I try to sum the values
on the report footer. On the footer I can calculate Rebates 1 - 3 by
putting:

=Sum(15*[Woods]+30*[Irons]+30*[Compri]) at the footer but I all I get is a
Zero when I try to put in =[Rebate 4]+[Rebate 5]+[Rebate 6]. If I try to
type in Sum([Rebate 4] + [Rebate 5] + [Rebate 6]) when the report runs it
comes up asking for Rebates 4 - 6. Like I said before the "Rebates" are not
in the original field list and I suspect that to be the problem but
unfortunately I don't know how to solve it.
Click to expand...


You're right, the aggregate functions operate on fields in
the record source table/query, they are unaware of controls
in the report.

The straightforward way to deal with this issue is to
calculate the values in the report record source query
instead of in controls in the report. Then the report can
do the Sum on the results of the query.
 
Back
Top