The IIF() Function

The IIF() function is unique to Crystal reports. It 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, the function returns whatever is in the third parameter. This function can return any data type except for an array.

Although this 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, 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 a certain string may or may not be needed. 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, we want to make sure we don’t insert a “.” inappropriately. The IIF() function tests whether the middle name exists, and if it does it adds it to the string with the proper formatting.

‘Demonstrate using the IIF() function to create a user's full name
Dim FullName As String
FullName = {Person.FirstName} & " " & IIF({Person.MI}<>"", {Person.MI} & ". ", "") & {Person.LastName)

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
Dim FullName As String
Dim MI As String
If {Person.MI}<>"" Then
MI = {Person.MI} & ". "
End If
FullName = {Person.FirstName} & " " & MI & " " & {Person.LastName}

This example shows that using the If 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. However, if the If statement were a lot more complicated, then using an IIF() function instead (or the other functions that are mentioned next) might make your code worse off.