Total Sum Function Not Working with IIF Statement

[morton]

Hello!

I am currently working on building a repair log and having trouble calculating a total for all the parts that were used in the repair. I used the Total Sum function built into MS Access 2007. This function behaves properly when I do a simple calculation such as multiplying Part Price, Quantity, and Discount. However, I need to modify the calculation to account for repairs that are done under warranty and thus are free.



My approach for this was to add a Yes/No field called Warranty and modify my calculation to an IF statement. Below is the expression I created in the Control Source. When selecting Sum in the Total dropdown, nothing seems to happen.


IIF([warranty]=0, [partprice]x[discount]x[quantity], 0)



Please offer any suggestions you can.


Thank you everyone!

 

[Minty]

To multiply you use the * operator not x .
And if any of those values are null you won't get an answer, so you may want to wrap each of them in a Nz([partprice],0)

 

[Peter Reid]

If Warranty is a Yes/No field you should be able to use

IIF(Not([warranty]), [partprice]*[discount]*[quantity], 0)

Note that I've also replaced the each x with an * - not sure if that was in your actual calculation

 

[plog]

If warranty is a Yes/No field, no need to do any test:

[warranty]*[partprice]*[discount]*[quantity]*-1

 

[morton]

If warranty is a Yes/No field, no need to do any test:

[warranty]*[partprice]*[discount]*[quantity]*-1

Thank you! This worked with some minor tweaking (tweaking that I don't even understand yet).

Instead of using "*-1" at the end, I placed it after [warranty] and was able to get the total sum function to work after doing it. Not really sure why but thanks for leading me to the right answer.