Sum sub-totals on a report by crieteria

[louonline]

Hi,
I have problem with a report based on a form used as as invoice.
the report prints the total for all the invoices for a particular user
entered date(or date range).
This works fine as far as getting an overall total but what I want is a
break down of the different type of payments.
Now when an invoice is paid (Amount_Tend), it can be paid by (Paid_By),
'CASH', 'EFTPOS' or 'CHEQUE'.
What's needed is some code to pull out the sub-totals these different
payments and print them at the top or bottom of the page.

Regards,
Lou

 

[Allen Browne]

Try putting something like this into the Control Source of the text box in
your Report Footer:

=Sum(IIf([Paid_By] = 'CASH', [Amount_Tend],0))

 

[louonline]

Hi Allen,
Thank you,

I cut and pasted your code into a text box in my report footer but I'm
getting an error '3075' stating Extra ) in my expression.
Any clues?

Regards,
Lou

 

[Allen Browne]

The expression has 2 opening brackets and 2 closing ones, so I don't
understand the error message.

You could try putting the expression into the Field row in a query so you
have a field to sum, but it should work in the report. You must use your
actual field names of course.

I have assumed from your description that the Paid_By field is a Text field,
not a Number field with a combo hiding the fact that it is text.

 

[louonline]

Thanks Allen,
I think I see where the problem is.

Reards,
Lou

 

[louonline]

Hi Allen,
the solotion looked so simple until I realised that the actual field I
want is the "InvoiceTotal" not the "Amount_Tendered".
Since the "invoceTotal" is a calculated field its made it hard to work
on it on my report.
I've managed to get the "InvoiceTotal" of each invoice by doing the
same calculation in the Report as I did in the Form, (that made me feel
good) but I can't segregate the total amount of all the invoices
according to the type of payment ("cash", "eftpos", "cheque".
I played around trying to create a query but I just got into a bigger
mess!
Any suggestions would be welcomed.
Regards,
Lou