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