IIF is Too Complex

[Joe]

Hello,

I have an IIF statement for a rate category that I need to implement onto a
strat report. However, when I enter my statement on to access it states that
its too complex. I understand since its kind of long...

My question is, how can I write it VB and pass it on to a query?

Here is the code and statement and it is really long :-(

IIF([int_rate] > 11.500, "11.500+",
IIF([int_rate] > 11.250, "11.251 - 11.500",
IIF([int_rate] > 11.000, "11.001 - 11.250",
IIF([int_rate] > 10.750, "10.751 - 11.000",
IIF([int_rate] > 10.500, "10.501 - 10.750",
IIF([int_rate] > 10.250, "10.251 - 10.500",
IIF([int_rate] > 10.000, "10.001 - 10.250",
IIF([int_rate] > 9.750, "9.751 - 10.000",
IIF([int_rate] > 9.500, "9.501 - 9.750",
IIF([int_rate] > 9.250, "9.251 - 9.500",
IIF([int_rate] > 9.000, "9.001 - 9.250",
IIF([int_rate] > 8.750, "8.751 - 9.000",
IIF([int_rate] > 8.500, "8.501 - 8.750",
IIF([int_rate] > 8.250, "8.251 - 8.500",
IIF([int_rate] > 8.000, "8.001 - 8.250",
IIF([int_rate] > 7.750, "7.751 - 8.000",
IIF([int_rate] > 7.500, "7.501 - 7.750",
IIF([int_rate] > 7.250, "7.251 - 7.500",
IIF([int_rate] > 7.000, "7.001 - 7.250",
IIF([int_rate] > 6.750, "6.751 - 7.000",
IIF([int_rate] > 6.500, "6.501 - 6.750",
IIF([int_rate] > 6.250, "6.251 - 6.500",
IIF([int_rate] > .001, "6.250 Or Less","Undefined")))))))))))))))))))))))

 

[Tom Wickerath]

Hi Joe,

In VBA code, you can use a SELECT Case....End Select to accomplish this.

You might be able to use the built-in Switch function, directly within the
query, without having to go to VBA code. Look up the Switch function in Help.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Brendan Reynolds]

Hello,

I have an IIF statement for a rate category that I need to implement onto
a
strat report. However, when I enter my statement on to access it states
that
its too complex. I understand since its kind of long...

My question is, how can I write it VB and pass it on to a query?

Here is the code and statement and it is really long :-(

IIF([int_rate] > 11.500, "11.500+",
IIF([int_rate] > 11.250, "11.251 - 11.500",
IIF([int_rate] > 11.000, "11.001 - 11.250",
IIF([int_rate] > 10.750, "10.751 - 11.000",
IIF([int_rate] > 10.500, "10.501 - 10.750",
IIF([int_rate] > 10.250, "10.251 - 10.500",
IIF([int_rate] > 10.000, "10.001 - 10.250",
IIF([int_rate] > 9.750, "9.751 - 10.000",
IIF([int_rate] > 9.500, "9.501 - 9.750",
IIF([int_rate] > 9.250, "9.251 - 9.500",
IIF([int_rate] > 9.000, "9.001 - 9.250",
IIF([int_rate] > 8.750, "8.751 - 9.000",
IIF([int_rate] > 8.500, "8.501 - 8.750",
IIF([int_rate] > 8.250, "8.251 - 8.500",
IIF([int_rate] > 8.000, "8.001 - 8.250",
IIF([int_rate] > 7.750, "7.751 - 8.000",
IIF([int_rate] > 7.500, "7.501 - 7.750",
IIF([int_rate] > 7.250, "7.251 - 7.500",
IIF([int_rate] > 7.000, "7.001 - 7.250",
IIF([int_rate] > 6.750, "6.751 - 7.000",
IIF([int_rate] > 6.500, "6.501 - 6.750",
IIF([int_rate] > 6.250, "6.251 - 6.500",
IIF([int_rate] > .001, "6.250 Or Less","Undefined")))))))))))))))))))))))

You could use a Select Case in VBA, but really, that's crying out to be
table-driven. If you had a table, called say Rates, with LowRate, HighRate
and RateText, your query might look something like ....

SELECT SomeTable.int_rate, Rates.RateText FROM SomeTable INNER JOIN Rates ON
SomeTable.int_rate >= Rates.LowRate AND SomeTable.int_rate <= Rates.HighRate

The query designer can't display this type of join in graphical design view,
you'll have to switch to SQL view.

 

[Fred]

Or mathematically round your interest rate down to the nearest "1/4%+.001%"
(e.g. 7.251) make and link a 2-field table with those values plus your
desired text, and then just load the text from the linked record.

 

[Lance]

It looks like you only need 3 conditions
1 for if it's above 11.5
1 for if it's below 6.25
and 1 for everything in between.

You've got a simple mathematical progression there ( + .25 per condition ).
All you need for the middle condition is something like

(Int(int_rate / 0.25) * 0.25) & " to " & (Int(int_rate / 0.25) * 0.25) +.25