Report problem in Access 2003

Question

Report problem in Access 2003

Paul Sp

SSCrazy

Points: 2314
More actions
August 23, 2005 at 9:36 am

#66799

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Nicole Bowman SSCommitted Points: 1555 More actions · Answer 1

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.

Paul Sp SSCrazy Points: 2314 More actions · Answer 2

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

Leo Hesen Ten Centuries Points: 1139 More actions · Answer 3

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

Paul Sp SSCrazy Points: 2314 More actions · Answer 4

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

Report problem in Access 2003

Cookies on SQLServerCentral