Applying a "Floor" of Zero to Query Results

[wojosh6]

How can I apply a "floor" of zero to the result of a query? For example, I have a field which takes the difference between 2 fields (from another query) & returns negative numbers, positive numbers, and 0's. I would like the negative results that are returned to simply be 0. Thanks for the help!

[CJ_London]

use the iif function

iif(result<=0,0,result)

[wojosh6]

Is there any other way besides using iif? My database is already crazy slow because I have about 8 other iff's within my queries & I'm dealing with hundreds of thousands of rows of data on my tables. If that's the only way I'll roll with it, but just thought I'd ask...

Also, thanks for the quick reply!

[CJ_London]

not really that will be 'quicker'

you could try the choose function

choose((result<0)+2,result,0) or choose(abs(result<0)+1,0,result)

If you are using nested iifs, try the switch function which is a bit like a case statement

[CJ_London]

just a thought, if you are using iif's to detect nulls, try using the nz function

so instead of iif(value is null,0,value)

use

nz(value,0)

[ssanfu]

In place of IIF(), could also use

result * ((result >= 0) * -1)

or

result * Abs(result >= 0)

[CJ_London]

result * Abs(result >= 0)

like that one ssanfu

[June7]

Re: use of IIf() and Nz() in query http://allenbrowne.com/QueryPerfIssue.html

Why do substitution in query? Why not expression in textbox on form or report?