IIf Condition in MS Access

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.