This is an excerpt from the book Crystal Reports Encyclopedia. Click to read more chapter excerpts.

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 Basic syntax 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} & ". "
Else
         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 and quick to type in. However, if the IIF() statement were complex, then using it might decrease the readability of your code.

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 Then statement and the Case statement. You can use it when the range of possible values is relatively small and sequential.

The Choose() function works by passing it an index number as the first parameter and a list of return values after it. The index number is used to determine which value is returned. The first position in the list of return values is indexed at 1. 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.

If the index is a value that is greater than the number of items passed, then the last item in the list is returned. For example, if the index number is 3 and there are five values in the list, then the third item would be returned. If the index number were 10 instead, then the fifth item would be returned because it is last in the list.

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

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

An example of displaying a person's place in a race using text is as follows. The range of possible values is 1 to the number of participants and this function will only consider the first three positions. Everyone else is given a generic classification.

Dim Position as String
Position = Choose({Race.Position}, "First Place", "Second Place", "Third Place", "Sorry, you were not one of the top three finishers.")
Formula = Position

The Switch() Function

The Switch() function is a slight variation of the Choose() function. The Switch() function uses pairs of test conditions and return values. The first parameter is an expression to test and the second parameter is a result value that is returned if the expression is true. This is repeated with every two parameters being in one group.

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

Var = Switch(condition1, result1, condition2, result2, ....)

What makes this unique from the If Then and Case statements is that every parameter is evaluated before a result is returned from the function. Even if the first parameter is true, all the remaining parameters are still evaluated. This can have good or bad consequences depending upon your needs. The result can be bad because there could be a performance issue if you are passing it time-intensive functions. It could also result in an error being raised if every condition wasn't meant to be evaluated (e.g. divide by zero errors). It can be good if you want to force various functions to be called prior to returning a value. It can perform a list of financial functions prior to returning a value. This function can return any data type except for an array. The data types of each result must be the same.

To read all my books online, click here for the Crystal Reports ebooks.