The IIF() Function

The IIF() function is a shortcut for the standard If statement. Its purpose is to put both the True and False actions on the same line. It consists of three parameters. The first parameter is the test condition. If the test condition is True, the function returns whatever is in the second parameter. If the test condition is False, then the function returns whatever is in the third parameter. This function can return any data type except for an array.

Although the IIF() function is convenient because you can condense a multi-line If statement into one line, there are two restrictions. The first is that the second and third parameters can only be a constant, a variable or a function. You can’t put a statement or code block within these parameters. The second restriction is that both parameters must be the same data type.

The syntax for the IIF() function is as follows:

var := IIF(condition, true_result, false_result);

I frequently use the IIF() function when concatenating strings together and one of the strings is optional. Since it is a function, I make it return a string. The following example creates a person’s full name. If the middle initial wasn’t entered into the database then we want to make sure we don’t insert a “.” inappropriately.

//Demonstrate using the IIF() function to create a user’s full name
StringVar FullName;
FullName := {Person.FirstName} & ” ” & IIF({Person.MI}<>“”, {Person.MI} & “. “, “”) & {Person.LastName);

This IIF() function tests whether the middle name exists, and if it does, it adds it to the string with the proper formatting.

For purposes of comparing conditional functions with conditional structures, the following example is the same except that it uses an If Then statement.

//Demonstrate using the If Then statement to create a user’s full name
StringVar FullName;
StringVar MI;
If {Person.MI}<>“” Then
MI := {Person.MI} & “. “
Else
MI := “”;
FullName := {Person.FirstName} & ” ” & MI & ” ” & {Person.LastName};

This example shows that using the If Then statement requires more coding. However, it does have the benefit of being easier to understand. It’s a matter of personal preference as far as which one you choose to use. Personally, I always choose the IIF() function because it is an easy function to read and quick to type in. However, if the IIF() function were complex, then using it might decrease the readability of your code.

Do not use the IIF() function to check for Divide By Zero errors. For example, the following code will cause an error:

IIF({Emp.NumberSales}<>0, {Emp.TotalSales}/{Emp.NumberSales}, 0);

The IIF() function executes every argument before returning a result. In this example, even if the number of sales is zero, the function will still attempt to divide the total sales by the number of sales and give you an error. You should use the standard If-Then statement when trying to handle any possible errors that could occur in a formula.