The iif function determines the value it returns based on what type of expression?

How to Use an IIF Function in Access

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 <> field with the field you want to apply the logical test. Replace the <> and <> 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(<>, <>, (<>).

FREE Quick Reference

Click to Download

Free to distribute with our compliments; we hope you will consider our paid training.

next → ← prev

IIf Function

This function is used to implement the if condition in MS Access. Just like in any other programming language, the IIf function in MS Access also evaluates an expression. Based on the evaluation, it evaluates whether the expression is True or not and performs the actions accordingly.

Syntax of IIf function

The syntax of the IIf function is as follows:

Arguments Passed in the IIf function

There are three parameters that are passed in the IIf function. These parameters are as follows:

Value Returned by the IIf function in MS Access

Two possible values or arguments can be passed into the IIf function. It returns the first value if the expression in the IIf function evaluates true, and it will return the other value if it is evaluated as false.

Note: When using the IIf function in MS Access, the user must ensure that both the truestat and the falsestat are valid values or expressions. The IIf function always checks both truestat and falsestat in the function, even though it only returns either value. Because it does not simply terminate after returning the value, then the user needs to ensure that it does not create any undesirable results.

For example, Even if the function returned the value assigned in the truestat. The function will further check the falsestat, and if the value is invalid, such as if the result is divided by zero, then the IIf function will return an error.

Implementing the IIf function in a Form or Report in MS Access:

Suppose you have a database that contains the data from an e-commerce website. It contains a Product table that contains information about the sales, delivery, and customer name. One column in the table is named Country, which contains the name of the countries where the product is delivered.

In a form, you want to assign an employee such that the company can efficiently communicate with clients from different countries. If the person is a resident of China, then the preferred will be "Chinese," but if the Country is any other than China, then the user will have to use some other language to communicate.

To easily facilitate communication, the user can use the IIf function. The function is located inside the Control Source property.

Let's use the same example to see how you will use the IIf function.

Add the above command in you're your Control Source property.

Open the form in the Form view to check that the IIf condition is working properly. When the user fills in the details in the form and the user ever enters the value as China in the country value. Then the control will automatically display Chinese as the Language, and if the Country takes any other language, it will display Other Language.

The IIf function can also be used to implement complex expressions as well. The user can execute any type of expression as part of an IIf statement. It is similar to the If-else statement in the other programming languages. It simply tests the expression, and based on whether the expression is true or false, it executes the statement. In MS Access, the user can use several IIf statements in the same line. The user can also perform nesting using the IIf function. This enables the user to evaluate a series of dependent conditions and compute complex decisions.

Let us further discuss the above example. This time we will create the command for several countries. This will test the value for the country column.

The user will be able to compute different values for countries other than China, allowing them to specify more communication languages.

Now, understanding the above command: The function will check if the Country's name is China. If yes, then it will return Chinese. If not, then as there is no other part, it will execute the next if function. This time it will check if the value entered is Japan or not. If yes, then it will return Japanese. Otherwise, it will move further. Now moving to the last IIf command, it will check if the Country is India, then it will return Hindi, and since this is the last statement, we have also added a result that will execute if the expression still returns false.

Note: Only add the falsestat in the last statement to ensure that every IIf condition is evaluated before returning the result.

Now let us take one more real-life scenario in which the IIf function can be used.

Suppose you are working in a library. As the librarian, you must keep a database for all the borrowed books and their due dates. So, you have created a table named borrowed in the database, and in that table, it has a column named Duedate, which contains the date when the particular date is Due. You can use the IIf function here to determine the status of the borrowed book and use it to determine whether the book has passed its due date or not.

Implement the following command to do so in your form:

Note: Here, we have also used the Date() function. It is used to return the current date.

When the user fills in the details in the form, it will ask the user to enter a date, and the date entered will be compared by the user to the current date. If the date entered in the form is less than the data returned by the Date(), then the control will display "Due Date Passed" as a result, and if it is equal, then it will return "Due Tomorrow" and for any other date entered in the form it will return "Not Due".

Note: You can also use the logical operators in the expression for the IIf function. This allows the user to evaluate more complex expressions using operators like "And" or "Or" in the expr argument. But to implement these logical operators, the user is required to enclose the logical expressions in the Eval function.

Implementing the IIf function in the query

The IIf function makes calculated files in complex databases by implementing the function in queries. The syntax of the IIf function in the query is the same in the Control Source Property. The only exception is that when executing the queries, the user must preface the expression with an alias field, and when using it in the query, you need to replace the equal sign (=) with a colon instead.

Let us implement the above example into a query. To execute the query, you need to type the given query in the Field row of the query design grid.

Here, the Language is the field alias.

Next TopicMs Access Report

← prev next →

What are the three parts of an IIF function?

What is IIF function in SQL?

Which function would you use to retrieve data from a subsequent row?

Does access have an IF function or something similar?