Don't want negatives to be included in query

  • Thread starter Thread starter Joy Rose
  • Start date Start date
J

Joy Rose

How do I eliminate negatives in a query; i.e.

Total customer sales = Sum of quantity*unit price.

However, customer may have a trade-in or allowance that appears as a
negative number in unit price. I do not want the negative to be included in
the calculation.

Thanks,
JR
 
Not quite sure what you want here. You can eliminate the records by putting
criteria against Unit Price field or you can use an IIf statement in the calculation.

SUM(IIF([Unit Price] <0,0,[Unit Price])*Quantity)

So anytime the unit price is negative you end up with Zero for that item

Otherwise, assuming this is a total query, add Unit Price to the query again and
apply criteria against it:

Field: [Unit Price]
Total: WHERE
Criteria: <0

NOTE that this is an additional column.
 
Thank you so much -- It worked the first time. I would still be struggling
without your help.
John Spencer (MVP) said:
Not quite sure what you want here. You can eliminate the records by putting
criteria against Unit Price field or you can use an IIf statement in the calculation.

SUM(IIF([Unit Price] <0,0,[Unit Price])*Quantity)

So anytime the unit price is negative you end up with Zero for that item

Otherwise, assuming this is a total query, add Unit Price to the query again and
apply criteria against it:

Field: [Unit Price]
Total: WHERE
Criteria: <0

NOTE that this is an additional column.

Joy said:
How do I eliminate negatives in a query; i.e.

Total customer sales = Sum of quantity*unit price.

However, customer may have a trade-in or allowance that appears as a
negative number in unit price. I do not want the negative to be included in
the calculation.

Thanks,
JR
Click to expand...
Click to expand...
 
Back
Top