Sum(IIF(.... w/ calculation

[razorkat99]

I am trying to calculate a field based on specific criteria....thought about in the query, but not sure how, so I figured I could put it in the Control Source in my report.

I want to multiply my Service_Performed field * $1.00 if = to "Disconnect" or * $2.00 if = to "Move"

I only got this far:
=Sum(IIf([Service_Performed]="Disconnect" ......fill in the blanks, Sum(IIf([Service_Performed]="Move"....fill in the blanks))))

I need the fill in the blank expression.

I used =Sum(IIf([Service_Performed]="Disconnect",2)) just to see if I was on the right track 'cause I couldn't figure out how to do the multiplicaton part. This expression however, counts ALL records as a group instead of separate for each customer. Hence my thought of having to put my calculation in the query.

Can someone help please?

 

[roelandkim]

If you want to calculate it in a query, you can use a nested iif statement like below:

Calculation: IIf([Service_Performed]="Move",[NumberField]*2,IIf([Service_Performed]="Disconnect",[NumberField]*1))

It sounds like "Service_Performed" is a text field, so you can't multiply it by a number. You will need to multiply it by a number field, in this case it would be whatever you have setup in your table. I am guessing it is something like number of hours, so a disconnect for 3 hours would cost 3 dollars, and a move for 3 hours would cost 6 dollars.

Hope this helps.

 

[razorkat99]

I'm sure this would work if I had a number field to multiply the data by, but I don't. I'll have to think about this some more. Thanks anyway.

 

[Brianwarnock]

If you ever manage to multiply "move" by $2 then I'm sure we will all be fascinated by the answer.

Brian