Use an IIF Function

A function is a predefined formula that performs a calculation. For example, the SUM function adds several values together.

An IIF function evaluates a specified condition and returns one value if the condition is true, and another if the condition is false. For example, you could use the IIF function in an invoice to create a formula that would subtract a 5-percent discount from the invoice if the total were more than 500 dollars—otherwise, the IIF function wouldn't subtract anything. Or, you could create an IIF function to create a field that gives passengers a 50-dollar rebate if they fly first class and a 25-dollar rebate if they fly coach.

The IIF function contains three parts, or arguments:

• A logical test, which is a value or formula, that can be evaluated as true or false.
• The value that is returned if the logical test is true.
• The value that is returned if the logical test is false.

1. In Design View, click the Field row of a blank column in the design grid.
2. Click the Builder button on the ribbon.
   

   The Expression Builder appears. In the bottom-left of the window, the Expression Builder displays a list of several folders that contain information.

   For example, the Tables folder contains a list of all the tables in the current database. These folders are displayed in a hierarchical view. A plus symbol or a minus symbol next to a folder means a folder contains several subfolders. Normally, these subfolders are hidden. You can display the hidden folders within a folder by double-clicking the folder. To see the contents of a folder, simply select the folder—its contents will appear in the middle and left columns.

3. Click the + symbol for the Functions folder.
   

   The Functions folder expands and displays its contents. The Built-In Functions folder contains several hundred functions that are included in Access.

4. Click the Built-In Functions folder.
5. Click the Program Flow category.
6. Double-click the IIF function.
7. Replace the argument placeholders with the fields and values you want to use.
• Use the folders in the Expression Builder to replace the <<expr>> field with the field you want to apply the logical test. Replace the <<truepart>> and <<falsepart>> arguments with the values you want to use if the IIF statement is true or false.
• You can double-click to select any argument name so that you can replace it with your own value.
8. Click OK.
   

   The Expression Builder closes. Now you can give the new calculated control a more meaningful name.

9. In the new calculated field, replace the Expr1: label with a more meaningful field name.
10. Save and run the query.

Access displays the results of the query.

To enter the IIF function manually, without using the Expression Builder, click the Field row of a blank column in the design grid. Enter the field name followed by a : (colon). Type the expression using the syntax IIF(<<expr>>, <<truepart>>, (<<falsepart>>).