IIF function with multiple arguments

[RHONDAK72]

I want my query to return the text in quotes for the “Spend Category” field based the following criteria for the “Amount” field value.

IF Amount <= 10000.00 then Spend Category = “Up to $10,000.00”
IF Amount >10000.00 and <= 250000.00 then Spend Category = “$10,000.01 to $250,000.00”
IF Amount >250000.00 and <= 1000000.00 then Spend Category = “$250,000.01 to $1,000,000.00”
IF Amount >1000000.00 and <= 5000000.00 then Spend Category = “$1,000,000.01 to $5,000,000.00”
IF Amount >5000000.00 then Spend Category = “Greater than $5,000,000.00”

Is there a way to put all of this into one IIF statement within my Access query?

 

[Joe4]

Yes, you can nest up to 7 levels into an IIF statement.

Give a try and see how you do. If you get stuck, try a search for "Nested IIF Statements". There are a lot.

 

[james_lankford]

something like this

iif ( Amount <= 10000.00, "Up to $10,000.00", "" ) & 
iif ( Amount between 10000.01 and 250000.00, "$10,000.01 to $250,000.00", "" ) & 
iif ( Amount between 250000.01 and 1000000.00, "$250,000.01 to $1,000,000.00" , "" ) & 
iif ( Amount between 1000000.01 and 5000000.00, "$1,000,000.01 to $5,000,000.00", "" ) & 
iif ( Amount > 5000000.00, "Greater than $5,000,000.00", "" ) 
as [Spend Category]

 

[Joe4]

That would probably work too, though is far less efficient than doing a nested IIF statement (because you are having to evaluate 5 different IIF statements for every calculation, even if the first condition is met).

 

[jackd]

SpendCategory: IIf([Amount]<=10000,"Up to $10,000.00",IIf([Amount] Between 10000.01 And 250000,"$10,000.01 to $250,000.00",IIf([Amount] Between 250000.01 And 1000000,"$250,000.01 to $1,000,000.00",IIf([Amount] Between 1000000.01 And 5000000,"$1,000,000.01 to $5,000,000.00",IIf([Amount]>5000000,"Greater than $5,000,000.00","")))))

 

[boblarson]

Personally, if I have more than two or three IIF's (usually more than two) I will create a function to do the calculation and then call it instead of using an IIF. It will likely go much faster.

 

[james_lankford]

That would probably work too, though is far less efficient than doing a nested IIF statement (because you are having to evaluate 5 different IIF statements for every calculation, even if the first condition is met).

yeah, you're right
I only do it this way because its just a lot easier for me to write
lazy I guess