Math not taking place

[goslincm]

Can anyone see what is wrong with this UPDATE query:

UPDATE Year3TempDistressFactors SET Year3TempDistressFactors.3Score = IIf(IsNull(Yr1GeneralFundRevenue+Yr1GeneralFundExpenditure),Null,IIf(Yr1GeneralFundRevenue-(Yr1GeneralFundExpenditure/Yr1GeneralFundRevenue)<-0.01,0,1));

When this runs against existing data, all the score come back as 1 when some should come through as zero. My math is not taking place.

 

[traingamer]

You are subtracting a ratio

(Yr1GeneralFundExpenditure/Yr1GeneralFundRevenue)

from your revenue

Yr1GeneralFundRevenue

In order to be less than .01 your expenditures would have to be over 100 times greater than your revenue.

Your math is likely working - your formula is flawed. You likely want

...IIf([b]1[/b]-(Yr1GeneralFundExpenditure/Yr1GeneralFundRevenue)<-0.01,0,1));

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

Or just a parenthesis problem:
IIf((Yr1GeneralFundRevenue-Yr1GeneralFundExpenditure)/Yr1GeneralFundRevenue<-0.01,0,1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[traingamer]

And, of course, I meant 10000 times greater not 100 times greater. (Mathematically speaking, PHV's and my formula are equal.)

 

[goslincm]

Got it, thanks so much for the help