databasedev.co.uk - database solutions and downloads for microsoft access

IIF Function use in Microsoft Access:

Using the IIF Function in Microsoft Access will allow you to evaluate a specific condition and specify results whether the condition meets True or False values.

The syntax for the IIF Function in Microsoft Access is:

IIf(expr, truepart, falsepart)

The IIf function syntax has the following named arguments:

Part	Description
exp	Required. Expression you want to evaluate.
truepart	Required. Value or expression returned if expr is True.
falsepart	Required. Value or expression returned if expr is False.

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

Now let's consider the following example:

We have an application that tracks Product Purchases. These products are electrical components, and each product has the option of having an extended Guarantee purchased with it.

We would like to track Guarantee Renewal Dates, when the optional Guarantee is purchased and we use the following DateAdd function:

This will return the following results:

As you can see, the above results will return an additional field, showing the Guarantee RenewalDate based on the data having a GuaranteeStartDate and GuaranteePeriod being specified. As we stated earlier, this is an optional requirement, and the customer may not always wish to take out this option.

Therefore, if a record does not contain the information in either the GuaranteeStartDate or GuaranteePeriod the results will look like the following, indicating an Error in the resultant data:

To get around the problem of an #Error appearing in the results, we can wrap the DateAdd function in an IIF Statement. We do this by using the following syntax:

RenewalDate: IIf([GuaranteeStartDate] Is Null Or [GuaranteePeriod] 
    Is Null,"N/A",DateAdd("yyyy",[GuaranteePeriod],[GuaranteeStartDate]))

What this does is evaluate the condition, and checks the data to see if there is a value in either the [GuaranteeStartDate] or a value in the [GuaranteePeriod]. If either of these fields are Null (i.e. don't contain valid information), it will return a value of "N/A" in the RenewalDate field in the query results.

The following shows how the IIF Function will return the result:

You can use the IIF Function anywhere you need to evaluate a condition as to being either True or False.