all values less than 0 to equal 0 or null

[Guest]

I am trying to calculate the sum of the cost but the data I have has negative
values that in this particular instance I do not want to count in my total
cost. Is there a way to write an If Than statment that will make all of the
negative values equal zero or not be counted at all when I make cost grouped
by SUM?

 

[David Lloyd]

Kathryn:

IIF([Cost]>=0,[Cost],0)

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am trying to calculate the sum of the cost but the data I have has
negative
values that in this particular instance I do not want to count in my total
cost. Is there a way to write an If Than statment that will make all of the
negative values equal zero or not be counted at all when I make cost grouped
by SUM?

 

[Dale Fye]

Add a where clause between your select and the Group By portion of the SQL.

WHERE [Cost] > 0

HTH
Dale

 

[Guest]

I would try something along the lines of
Sum(IIf(myCol<0,0,myCol))

Hope This Helps
Gerald Stanley MCSD