---

MS Access 2003: Convert IF formula in Excel to IIF formula in a query

This MSAccess tutorial explains how to convert an Excel IF formula to an Access Iif function in query in Access 2003 (with screenshots and step-by-step instructions).

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))))))