Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TreasCG
Starting Member
2 Posts |
 Posted - 2008-05-18 : 17:03:37
|
| I'm trying to run this as one of the fields in my query:SB: (([yr1generalfundrevenue]-[yr1generalfundexpenditure])/[yr1generalfundrevenue])When I plug in real numbers for general fund revenue of 1,397,327 and general fund expenditure of $1,408,996 the SB field computes-0.008350944 number how can I get it to round this and compute only a -0.01?Both fields are set to currency in my table with decimal set to "auto".Can someone please help me? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
TreasCG
Starting Member
2 Posts |
Posted - 2008-05-19 : 11:54:27
|
| So working with the (Int) the below will work if my query is a select, however, I need to compute this and then update the result into the 5Score field of my table. Please tell me there is a way to do this?UPDATE Year3TempDistressFactors SET Year3TempDistressFactors.5Score = IIf(("Yr1GeneralFundRevenue-Yr1GeneralFundExpenditure")/[Year3TempDistressFactors]![Yr1GeneralFundRevenue]<=("int")-0.1,1,IIf(("Yr1GeneralFundRevenue-Yr1GeneralFundExpenditure")/[Year3TempDistressFactors]![Yr1GeneralFundRevenue]>("int")-0.01,0,IIf(IsNull([Year3TempDistressFactors]![Yr1GeneralFundRevenue]) Or IsNull([Year3TempDistressFactors]![Yr1GeneralFundExpenditure]),Null,Null))); |
 |
|
|
|
|
|
Rounding IIF function