Something wrong in this qry

[goslincm]

General Fund Revenue $4374760
General Fund Expenditure $4463512


I have the following query to produce a score for me but based on my numbers above I'm not getting the correct score and cannot see why.

UPDATE Year3TempDistressFactors SET Year3TempDistressFactors.5Score = IIf(Year3TempDistressFactors!Yr1GeneralFundRevenue-[Yr1GeneralFundExpenditure]/[Yr1GeneralFundRevenue]<-0.01,1,IIf(Year3TempDistressFactors!Yr1GeneralFundRevenue-[Yr1GeneralFundExpenditure]/[Yr1GeneralFundRevenue]>-0.01,0,IIf(IsNull(Year3TempDistressFactors!Yr1GeneralFundRevenue) Or IsNull(Year3TempDistressFactors!Yr1GeneralFundExpenditure),Null,Null)));

 

[GhostWolf]

Not needing the Null tests, I modified your SQL - and got 0 as the answer.

select iif(44374760-(4463512/44374760) < -.01,1,
iif(44374760-(4463512/44374760) > -.01,0))

(44374760 - 1.02 > -.01) = 0
.

 

[goslincm]

Sorry to be so confusing, this is driving me crazy.
GenFund Rev - GenFunExp / GenFunRev
$4,374,760 - $4,463,512/4,463,512 is GREATER THAN -.01 should it should come back a 1 not a zero.

 

[goslincm]

I had the nulls in there because if either field was empty I didn't want a zero, I didn't want any score for the null fields.

 

[GhostWolf]

Oh! Try this then:
select iif(44374760-(4463512/44374760) < -.01,0,
iif(44374760-(4463512/44374760) > -.01,1))

Though should get you what you want.

 

[PHV]

You wanted something like this ?
IIf(IsNull(Yr1GeneralFundRevenue+Yr1GeneralFundExpenditure),Null,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

 

[goslincm]

So when I put in the field names it will look like:Select iif([GenFundRevenue]-([GenFundExpenditure]/[GenFundRevenue]) < -.01,0

and then of course add the last part

 

[goslincm]

PH...I need to make sure if either GeneralFundRevenue or GeneralFundBalance are null that they do not get a score. Otherwise my calculation needs to be such that if general fund revenue - general fund expenditure / general fund revenue is less than -.01 they score a 1

 

[goslincm]

Brother, I have my 0 and 1 in the wrong part of my calculation. I flip-flopped it and now it works.

So sorry but I do appreciate all the quick help!

 

[PHV]

Which assertion is true ? This:
if general fund revenue - general fund expenditure / general fund revenue is less than -.01 they score a 1
or this ?
$4,374,760 - $4,463,512/4,463,512 is GREATER THAN -.01 should it should come back a 1 not a zero

My suggested formula shoulds return a Null value if either GeneralFundRevenue or GeneralFundBalance are null.

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

 

[GhostWolf]

You can put the separate Null tests at the front, and end up with something like:

iif(isnull(Yr1GeneralFundRevenue) or isnull(Yr1GeneralFundExpenditure), Null,
IIf(Yr1GeneralFundRevenue-(Yr1GeneralFundExpenditure/Yr1GeneralFundRevenue)<-0.01,1,0))

(Note the change at the end of the query to match your score specification in your last post.)

I like the way you shortened that, PH.

 

[GhostWolf]

My suggested formula should...

Thanks for the tip, PH. I've never seen it done that way.

 

[goslincm]

THanks for all the help with this and sorry for the delay response. The assertion that was true/is true is such:

if general fund revenue - general fund expenditure / general fund revenue is less than -.01 they score a 1

Otherwise, they need to score a zero.