B
Brad
I am wanting to sum up values in my report.
The formula I am using works in any of the
grouped footers, but not in the Report Footer.
When the formula is placed in the grouping
footers, it works perfectly. So I have
added up all the totals from the groupings
to see if the report footer displays the
correct value, and the difference is a
result of the 'if' portion of the formula
not working in the report footer.
Formula:
=IIf([Var1]=1,0,Sum([Week1])-Sum([Week1]*[Var1])-Sum
([Week1]*[Var2]))
Here is what the report does. I've tried
to explain it with a data table. If you
put the table into Excel, it may help.
Var1 (Rows) = Week1*Var1pre
Var2 (Rows) = Week1*Var2pre
Answer (Rows)= if(Var1pre = 100%,0,Week1-Var1-Var2)
Correct Answer = Sum(Answer Column)
Incorrect Answer =
Sum(Week1 Column)-Sum(Var1 Column)-Sum(Var2 Column)
Week1 Var1pre Var2pre Var1 Var2 Answer
53.00 100.00% 0.00% 53 0 0
528.00 100.00% 0.00% 528 0 0
0.00 0.00% 0.00% 0 0 0
0.00 100.00% 50.00% 0 0 0
-1.00 100.00% 50.00% -1 -0.5 0
79.00 100.00% 50.00% 79 39.5 0
0.00 63.75% 33.00% 0 0 0
203.00 63.75% 33.00% 129.412 66.99 6.5975
0.00 63.75% 33.00% 0 0 0
0.00 63.75% 33.00% 0 0 0
0.00 63.75% 33.00% 0 0 0
68.00 0.00% 0.00% 0 0 68
101.00 0.00% 0.00% 0 0 101
849.00 47.14% 0.00% 400.218 0 448.7814
643.00 0.00% 25.00% 0 160.75 482.25
0.00 100.00% 0.00% 0 0 0
0.00 31.67% 30.00% 0 0 0
509.00 31.67% 30.00% 161.200 152.7 195.0997
Correct Ans 1301.7286
Incorrect Ans 1262.73
If the formula is placed in a grouping footer
and the results are added up by hand, the
result will be equal to the Correct Ans. If
the formula is placed in the Report Footer,
the result will be the Incorrect Ans.
I know that the problem is the "if" portion of
the formula is not working because the difference
between the Correct and Incorrect answers
are the situations where Var1pre = 100%.
I have tried referencing controls (text boxes)
in the grouping footers, from a control (text box)
in the Report Footer, but Access just requests the
value.
I hope this makes sense, if not, please post
questions and I'll do my best to answer.
Thanks so much for taking the time to read and answer
my question, I really apreciate it.
Brad
The formula I am using works in any of the
grouped footers, but not in the Report Footer.
When the formula is placed in the grouping
footers, it works perfectly. So I have
added up all the totals from the groupings
to see if the report footer displays the
correct value, and the difference is a
result of the 'if' portion of the formula
not working in the report footer.
Formula:
=IIf([Var1]=1,0,Sum([Week1])-Sum([Week1]*[Var1])-Sum
([Week1]*[Var2]))
Here is what the report does. I've tried
to explain it with a data table. If you
put the table into Excel, it may help.
Var1 (Rows) = Week1*Var1pre
Var2 (Rows) = Week1*Var2pre
Answer (Rows)= if(Var1pre = 100%,0,Week1-Var1-Var2)
Correct Answer = Sum(Answer Column)
Incorrect Answer =
Sum(Week1 Column)-Sum(Var1 Column)-Sum(Var2 Column)
Week1 Var1pre Var2pre Var1 Var2 Answer
53.00 100.00% 0.00% 53 0 0
528.00 100.00% 0.00% 528 0 0
0.00 0.00% 0.00% 0 0 0
0.00 100.00% 50.00% 0 0 0
-1.00 100.00% 50.00% -1 -0.5 0
79.00 100.00% 50.00% 79 39.5 0
0.00 63.75% 33.00% 0 0 0
203.00 63.75% 33.00% 129.412 66.99 6.5975
0.00 63.75% 33.00% 0 0 0
0.00 63.75% 33.00% 0 0 0
0.00 63.75% 33.00% 0 0 0
68.00 0.00% 0.00% 0 0 68
101.00 0.00% 0.00% 0 0 101
849.00 47.14% 0.00% 400.218 0 448.7814
643.00 0.00% 25.00% 0 160.75 482.25
0.00 100.00% 0.00% 0 0 0
0.00 31.67% 30.00% 0 0 0
509.00 31.67% 30.00% 161.200 152.7 195.0997
Correct Ans 1301.7286
Incorrect Ans 1262.73
If the formula is placed in a grouping footer
and the results are added up by hand, the
result will be equal to the Correct Ans. If
the formula is placed in the Report Footer,
the result will be the Incorrect Ans.
I know that the problem is the "if" portion of
the formula is not working because the difference
between the Correct and Incorrect answers
are the situations where Var1pre = 100%.
I have tried referencing controls (text boxes)
in the grouping footers, from a control (text box)
in the Report Footer, but Access just requests the
value.
I hope this makes sense, if not, please post
questions and I'll do my best to answer.
Thanks so much for taking the time to read and answer
my question, I really apreciate it.
Brad