HomePrivacy PolicyFeedbackLink to usSite Map

MS Access: Convert IF formula in Excel to IIF formula in a query in Access 2003/XP/2000/97


Question:  I have a formula in Excel that I need to convert so that it will work in an Access 2003/XP/2000/97 query.

I would like the formula to read as follows:

if [quality] is greater than 0.99, then return 1
else if [quality] is between 0.97-0.989, then return 2
else if [quality] is between 0.95-0.969, then return 3
else if [quality] is between 0.91-0.949, then return 4
else if [quality] is less than 0.91, then return 5

The formula in Excel reads as follows:

=IF(L12="","",IF(L12<>"",IF(L12>=0.99,1,IF(AND(L12>=0.97),2,IF(AND(L12>=0.95),3,IF(AND(L12>=0.91),4,IF(AND(L12<=0.91),5)))))))

How would you convert this into an Access query formula?

Answer:  In Access, you could write the above formula as follows:

IIf(IsNull([quality]),Null,IIf([quality]>=0.99,1,IIf([quality]>=0.97,2,IIf([quality]>=0.95,3,IIf([quality]>=0.91,4,IIf([quality]<=0.91,5))))))