Sum Expression

[pht1991]

I am trying to build a conditional expression in the report footer.

I have a "fees collected" field for each record. I also have a "payment
method" field for each record (either "Cash", "check" or "on-line"). At the
bottom, I want to sum the total cash fees collected and the total check fees
collected in two separate fields (both fees are entered as "fees collected").

How would one go about writing this expression?

Thank you,
Laura E

 

[Ken Sheridan]

Laura:

Sum the values of [Fees Collected] multiplied by the return value of an
expression, using the IIf function, which returns 1 or 0 depending on the
value of [Payment Method], i.e. for cash fees:

=Sum([Fees Collected] * IIf([Payment Method] = "Cash",1,0))

and for check fees:

=Sum([Fees Collected] * IIf([Payment Method] = "Check",1,0))

BTW you'll sometimes see a method suggested using the absolute value of a
Boolean expression, e.g.

=Sum([Fees Collected] * Abs([Payment Method] = "Cash"))

Don't do it. It relies on the implementation of Boolean True or False
values as -1 or 0 in Access. Reliance on the implementation is bad
programming practice. Its what the head of one software company of my
acquaintance once called 'being unduly chummy with the implementation'.

Ken Sheridan
Stafford, England

 

[pht1991]

Wonderful - thank you so much!

Laura:

Sum the values of [Fees Collected] multiplied by the return value of an
expression, using the IIf function, which returns 1 or 0 depending on the
value of [Payment Method], i.e. for cash fees:

=Sum([Fees Collected] * IIf([Payment Method] = "Cash",1,0))

and for check fees:

=Sum([Fees Collected] * IIf([Payment Method] = "Check",1,0))

BTW you'll sometimes see a method suggested using the absolute value of a
Boolean expression, e.g.

=Sum([Fees Collected] * Abs([Payment Method] = "Cash"))

Don't do it. It relies on the implementation of Boolean True or False
values as -1 or 0 in Access. Reliance on the implementation is bad
programming practice. Its what the head of one software company of my
acquaintance once called 'being unduly chummy with the implementation'.

Ken Sheridan
Stafford, England

I am trying to build a conditional expression in the report footer.

I have a "fees collected" field for each record. I also have a "payment
method" field for each record (either "Cash", "check" or "on-line"). At the
bottom, I want to sum the total cash fees collected and the total check fees
collected in two separate fields (both fees are entered as "fees collected").

How would one go about writing this expression?

Thank you,
Laura E