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, then 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 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 then 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.

The Choose() Function

The Choose() function returns a value chosen from a list of values. The value returned is determined by an index that is passed to the function. This function is like a shortcut for the If statement and the Select Case statement. You can use it when the range of possible values is relatively small and sequential.

The first parameter is an index representing which item to return. The remaining parameters are the items to choose from. The index range starts at 1 (it’s not zero based). If the index is a value that is greater than the number of items passed, then the default value for the appropriate data type is returned (e.g. zero for numbers, “” for strings). This function can return any data type except for an array. As expected, each item in the list must be of the same data type.

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

Var = Choose(index, value1, value2, value3, …)