August 23, 2005 at 9:36 am
Hi
I have a silly problem - I'm sure with a simple solution.
Simplified, my report has a Detail Section containing two fields Cost and Paid. Cost is a currency field, Paid is a bit (checkbox).
In a Group Header is a staffmember's name.
I want to add a field to the Group Header which totals the Cost field only where the Paid field is 0 (unchecked). That is total all unpaid costs.
Many thanks
Paul
August 23, 2005 at 11:47 pm
Hi Paul,
The easiest way to do this is to add a field (I've called it Unpaid) to the query supplying the data to the report. Then put something like this as the field definition: Unpaid: IIf([Paid]=False,[Cost]). This will create a field that only has the unpaid amounts in it. This is very easy to sum in the report: =Sum([Unpaid]) as the control source for the field.
The field in the query is a dynamic field and does not need to reside in a table.
Of course this is all assuming that you are using a query to supply data to the report and not a table.
Cheers,
Nicole
Nicole Bowman
Nothing is forever.
August 24, 2005 at 3:29 am
Hi Nicole
Thanks for the reply.
Yes, that is a surefire way to do it. However, I have very much simplified my needs just for illustration purposes.
What I really need is to do it on-the-fly with a bit of code in the Report. I have done this before, but, for some unaccountable reason, I can't get it to work now.
I need to work out the cost of PT staff work according to whether they have previously been paid for the work or not. There is a simple and elegant way to accomplish this by testing the Paid field before adding the Costs - but I just can't seem to be able to recreate it. I obviously need a holiday.
Many thanks
Paul
August 24, 2005 at 3:42 am
Hello Paul
Create a textbox and set the following Control Source:
=Sum(IIf([Table]![paid],[Table]![Cost],0))
Adjust [Table] to the correct table name.
Succes
August 24, 2005 at 10:07 am
Thanks Leo
Unfortunately I can't get this to work for my Report. I do have a solution - not a very elegant one - but it will have to do.
Thanks again
Paul
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply