IIF Condition not working in Access16

[lokanathas]

=IIf( [Master]![MRPNew] >=1000,">1000",IIf( [Master]![MRPNew] >=700,"700-999",IIf( [Master]![MRPNew] >=600,"600-699",IIf( [Master]![MRPNew] >=500,"500-599",IIf( [Master]![MRPNew] >=400,"400-499",IIf( [Master]![MRPNew] >=300,"300-399",IIf( [Master]![MRPNew] >=200,"200-299",IIf( [Master]![MRPNew] >=100,"100-199",IIf( [Master]![MRPNew] >=0,"0-99",IIf( [Master]![MRPNew] <=-1000,">1000",IIf( [Master]![MRPNew] <=-700,"700-999",IIf( [Master]![MRPNew] <=-600,"600-699",IIf( [Master]![MRPNew] <=-500,"500-599",IIf( [Master]![MRPNew] <=-400,"400-499",IIf( [Master]![MRPNew] <=-300,"300-399",IIf( [Master]![MRPNew] <=-200,"200-299",IIf( [Master]![MRPNew] <=-100,"100-199",IIf( [Master]![MRPNew] <=-0,"0-99","NA"))))))))))))))))))

i heard that only 7 iif is possible, kindly suggest the alternate solution for the above slabbing.

 

[Ranman256]

if you have that many nested IIFs, dont use this, instead use
a lookup table
or
custom function.

you make a table with your values :
-600, "600-699"
-500, "500-599"

add it to your query to produce the result needed.
the photo shows how to use a table to do the lookup:

 

[jdraw]

You might be able to make use of the Partition function depending on your data/requirements.

 

[lokanathas]

Partition function will not allow the negative numbers. " The number can't be less than 0"

 

[Galaxiom]

Partition function will not allow the negative numbers. " The number can't be less than 0"

Looks like you don't care about the sign of the number anyway. Use the ABS() function to just get its magnitude. Then apply Partition().

 

[lokanathas]

if you have that many nested IIFs, dont use this, instead use
a lookup table
or
custom function.

you make a table with your values :
-600, "600-699"
-500, "500-599"

add it to your query to produce the result needed.
the photo shows how to use a table to do the lookup:
View attachment 67483

Done the same, but the issue is i have value in decimal also, so this is not possible..

 

[lokanathas]

Looks like you don't care about the sign of the number anyway. Use the ABS() function to just get its magnitude. Then apply Partition().

Thanks, its working, but i want the stop the slab/range till 1000, any value >1000 i want it to be tagged as only 1000 or with >1000 sign