Report Totals

[Paul Ilacqua]

I have a report that looks like this..............................

Date1 Date2 Date3
Part 12345
Planned 200 300 1800
Actual 100 100 778
-----------------------------------------------------------
Totals -100
-----------------------------------------------------------
Part 56789
Planned 300 300 1800
Actual 400 100 778
-----------------------------------------------------------
Totals 100 etc

How can I get the group footer formula to take the difference between the
actual and planned ?
It will always be a part number then an actual and a plan.

Thanks in advance
Paul

 

[Allen Browne]

Try something like this in the Control Source of a text box in the group
footer:
=Sum(IIf([Field2]="Planned", -[Date1], [Date1]))

 

[Paul Ilacqua]

Allen......
Thank You very much for the quick and spot on answer to my question. A
slight variation to your solution worked like a dream. You can't imagine the
complexity of my "other" solutions. The simplicity of the real fix is / was
amazing. I'm trying to create a "spreadsheet" type calculation in Access. I
guess I was on the right track by pulling the words "Actual" and "Planned"
into the result set allowing the calculation you suggested to work.
I hope someday I can return the favor.
Thanks
Paul

Try something like this in the Control Source of a text box in the group
footer:
=Sum(IIf([Field2]="Planned", -[Date1], [Date1]))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a report that looks like this..............................

Date1 Date2 Date3
Part 12345
Planned 200 300 1800
Actual 100 100 778
-----------------------------------------------------------
Totals -100
-----------------------------------------------------------
Part 56789
Planned 300 300 1800
Actual 400 100 778
-----------------------------------------------------------
Totals 100 etc

How can I get the group footer formula to take the difference between the
actual and planned ?
It will always be a part number then an actual and a plan.

Thanks in advance
Paul