Sum(IIF()) Help 2

[netnovadotnet]

Hello,

I'm trying to Sum the column [Rx] where the column [%ChangeRx] is greater then itself plus 2%.
This is what I did, and it returns only the negative percentages:

Sum(IIf([% Change Rx]>[% Change Rx]*1.02,[Rx],0))

Any Ideas?

And can I enter this into a report footer to do the calculations instead of a new query?

Thanks!

 

[lespaul]

Try using the abs function and see if that works!

Sum(IIf(ABS([% Change Rx])>Abs([% Change Rx])*1.02,[Rx],0))


Leslie

 

[traingamer]

how about:

Sum(IIf([% Change Rx]>[COLOR=red]([Rx]*1.02)[/color],[Rx],0))

traingamer

 

[traingamer]

because by the nature of math, [% Change Rx] * 1.02 can only be greater than itself if it IS negative.

traingamer

 

[lespaul]

damn that math nature!!


Leslie

 

[grande]

Yeah, what traingamer said. I'm pretty sure that's your problem.

Lespaul (a fine guitar), what is the ABS function used for? I've seen it many times, but never used it.

 

[lespaul]

it's the ABSOLUTE value of a number:

Abs(-3) = 3
Abs(3) = 3

(handle has nothing to do with guitars though!! I'm Leslie and my husband is Paul, so together we're LesPaul!!!)

HTH

Leslie