=Sum(Abs([field]="Value")*[Field]

  • Thread starter Thread starter gumby
  • Start date Start date
G

gumby

I want to sum [Tuition Cost] but only if [Final Action] equals either
Approved, Pending, or Obligated. The control source below works fine. I
am just interested to find out if a easier way exists.

=Sum(Abs([Final Action]="Approved")*[Tuition Cost])+Sum(Abs([Final
Action]="Pending")*[Tuition Cost])+Sum(Abs([Final
Action]="Obligated")*[Tuition Cost])



Othe ways I have tried.

=Sum(Abs([Final Action]="Approved" And "Pending" And
"Obligated")*[Tuition Cost]

=Sum(Abs([Final Action]="Approved" And [Final Action]="Pending" And
[Final Action]="Obligated")*[Tuition Cost]



Thanks,
David
 
You have a table of [Final Action] values with an additional field for
[ApplyCost] which stores 0 for all records other than "Approved", "Pending",
and "Obligated". These three records contain a 1 in the ApplyCost field.

Then add this table to your report's record source and join the [Final
Action] fields. Your final expression then becomes:
=Sum([Tuition Cost] * [ApplyCost])

Maintain tables, not expressions.
 
Back
Top