If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating in a Report
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. |
#2
|
|||
|
|||
Calculating in a Report
Avid Reader wrote:
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. 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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|