Iif statement too complex

[razorking]

I know this is a common question, lots of stuff found in Google searches. However I am still not sure how to best handle this. Have a query that looks like this:

IIf([LFGen]>55.99,"Z",IIf([LFGen]>51.99,"Y",Iif([LFGen]>47.99,"X",Iif([LFGen]>43.99,"W",Iif([LFGen]>39.99,"V",Iif([LFGen]>35.99,"U",
Iif([LFGen]>31.99,"T",Iif([LFGen]>27.99,"S",Iif([LFGen]>23.99,"R",Iif([LFGen]>21.49,"Q",Iif([LFGen]>17.99,"P",Iif([LFGen]>15.49,"O",
Iif([LFGen]>13.99,"N",Iif([LFGen]>12.49,"M",Iif([LFGen]>10.99,"L",Iif([LFGen]>9.25,"K",Iif([LFGen]>8.25,"J",Iif([LFGen]>7.25,"I"
,Iif([LFGen]>6.25,"H",Iif([LFGen]>5.25,"G",Iif([LFGen]>4.25,"F",Iif([LFGen]>3.25,"E",Iif([LFGen]>2.85,"D",Iif([LFGen]>2.45,"C"
,Iif([LFGen]>2.01,"B",Iif([LFGen]>1,"A"))))))))))))))))))))))))))

It is too complex. I believe I can use a Case statement in VBA - but I am not skilled in VBA and not entirely sure how to pull it off.

I also believe maybe there is a way to have the numeric values above be in a separate table (with the corresponding A,B,C etc values) and use that in my query. But, if that is an option - I am not quite seeing how that would work).

Any suggestions?

Thanks!

 

[Jeanette Cunningham]

How to use a separate table for this is not exactly straight forward.
Here's a link to Doug Steele's approach to this problem.
The article's available for free at http://www.vb123.com/kb/200412_ds_aa.htm

 

[razorking]

Thanks Jeanette,

I was able to use the article to make something that is working - at least it has passed my early tests.

Here is a sample of it - maybe it will be useful to someone else.