Sum with conditions in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If a customer ordered 5 units and I ship him 4 the following expression works
But, If he ordered 5 and I wanted to send him 6 for his trouble on my invoice
it shows -1 in the back order. my question is if the ship amount is more than
the ordered how can I get it to still show "0".
Here is the expression I am trying to build on. Thanks in advance.

=IIf([Quantity]="ShipQuanity","0",[Quantity]-[ShipQuanity])
 
It looks like you are mixing string values with field names and numeric
values. Try
BackOrder
=IIf([ShipQuantity]>[Quantity] , 0 , [Quantity] -[ShipQuantity])
 
Back
Top