MS Access —

Using an IIf Function

Functions are used to create more complicated calculations or expressions than operators can. For example, the adds several values together, and the IPmt function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

The syntax for the IIf function

Here the IIf function evaluates the value in the First Class field and returns 50 if the First Class field is True and 25 if the First Class field is False.

There are several hundred in Access, but all of them are used in a similar way: the name of the function, followed by the arguments in parenthesis. An argument in Access is the value a function uses to perform its calculation not the heated disagreement you have over political views. For example, the argument in the formula p r2 would be r, or the radius, used to find the area of a circle.

This lesson introduces a very useful database function: the IIf function. The IIf function is a or because it evaluates a condition and returns one value if the condition is true and another value 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 dollar otherwise, the IIf Function wouldn't subtract anything.

The IIf function contains three arguments, as shown in figure. Since you can use the to help you create IIf function formulas, you really don't need to memorize the syntax of the function.

In this lesson you will use the 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, as shown in figure.

  1. Click the qryRebate query and then click the Design button.

    You need to create a calculated field that will determine how much of a rebate passengers should receive. Create this field in the first blank Field row.

  2. Click the blank Field row in the fifth column and click the Build button on the toolbar.

    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 folderits contents will appear in the middle and left windows.

    To use one of the built-in functions, double-click the Functions folder and select the in the bottom-left window.

  3. Double-click the Functions folder in the bottom-left window.

    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.

    When you select the Built-In Functions folder in the left window, the middle window displays the function categories you can use, and the right window displays the functions in the selected category. If you know the category of the function you want to use, you can select it in the middle window to narrow down your choices in the right window.

  5. Scroll down the middle window and find and click the Program Flow function category.

    The right window displays the Program Flow functions.

  6. Double-click the IIf function in the right window.

    Access adds IIf (expr, truepart, falsepart) to the expression box. Now that you know the proper syntax of the IIf function, you need to replace the argument names with the data values. You can double-click to select any argument name so that you can replace it with your own value.

  7. Double-click the expr argument.

    You need to specify the logical testif the passenger flew first class or not.

  8. Double-click the Tables folder in the left window, click the tblCustomers folder, and then double-click the First Class field in the middle window.

    The Expression Builder adds the First Class field to the expression. Finish the rest of the logical test.

  9. Type =true.

    Your expression should read IIf ([tblCustomers]![First Class]=True, truepart, falsepart). Now you have to replace the truepart and falsepart arguments with the values you want to use if the IIf statement is true or false.

  10. Double-click the truepart argument and type 50, then double-click the falsepart argument and type 25.

  11. Click OK.

    The Expression Builder closes. We need to give the new calculated control a more meaningful name...

  12. In the new calculated field, replace the Expr1: label with Rebate:

    Let's see the results of the new query.

  13. Click the Run button on the toolbar.

    The IIf expression in the new Rebate field evaluates the First Class field and returns a 50-dollar rebate if the customer flew first class, and a 25-dollar rebate if not.

    Save your changes.

  14. Save your changes and close the query.

To create an iif (if...then) function:

  1. display the query in design view.

  2. click the field row of a blank column in the design grid.

  3. enter the field name followed by a colon (:).

  4. type the expression using the syntax iif (expr, truepart, falsepart)

    or...

    click the build button on the toolbar. double-click the functions folder in the bottom-left window, click the built-in functions folder, scroll down the middle window and click the program flow category, then scroll down the right column and double-click the iif function. replace the parts of the pasted iif function with the fields and values you want, then click ok.