### Switch (expr1, value1, expr2, value2, ..., exprN, valueN)

Basic and Crystal syntax.

##### Arguments

- expr1, expr2, ..., exprN are Boolean expressions.
- value1, value2, ...., valueN are the possible values that may be returned. All the values must be of the same type. A value can be any simple type (Number, Currency, String, Boolean, Date, Time or DateTime) or range type (Number Range, Currency Range, String Range, Date Range, Time Range or DateTime Range), but it may not be an array.

##### Returns

One of the values in the value list value1, value2, ...., valueN. The type of the returned value is the same as the type of the values.

##### Action

The argument list for Switch consists of pairs of expressions and values. Switch evaluates the expressions from left to right, and returns the value associated with the first expression to evaluate to True.

For example, if expression1 is True, the Switch returns value1. If expression1 is False and expression2 is True, then Switch returns value2. If expression1 and expression2 are False and expression3 is True, then Switch returns value3.

If all of the expressions are False, then Switch returns a default value. (The default value returned depends on the type of the values in the value list. For example, if the values are of Number type, the default value is 0 and if the values are of String type, the default value is the empty string ("").)

##### Typical uses

Switch can be used instead of If-Then-Else operators or Select statements (Basic syntax) and Select expressions (Crystal syntax) in some situations.

One situation where Switch may be better than a control structure is when writing record selection formulas so that they can be pushed down to the database server. See below for an example as well as Advanced record selection formulas for an explanation of the techniques involved.

##### Examples

The following examples are applicable to both Basic and Crystal syntax:

Suppose that a company wants to classify orders into "large", "medium" or "small" based on the order size:

Rem Basic syntax
formula = Switch ({Orders.Order Amount} > 5000, "large", _
{Orders.Order Amount} > 1000, "medium", _
True, "small")
//Crystal syntax
Switch ({Orders.Order Amount} > 5000, "large",
{Orders.Order Amount} > 1000, "medium",
True, "small")

If {Orders.Order Amount} is greater than $5,000 then the formula returns the String value "high". Otherwise, if {Orders.Order Amount} is greater than $1,000, the formula returns the String "medium". Otherwise, the formula returns "small".

Here is an example showing how to use the Switch function for writing efficient record selection formulas that can be pushed down to the database server. Writing this formula using If-Then-Else operators or Select statements (Basic syntax) and Select expressions (Crystal syntax) would not let it be pushed down.

See Advanced record selection formulas for further explanation of the techniques involved.

Note: This example is in Crystal syntax since record selection formulas edited with the Formula Editor must be in Crystal syntax only.

##### Sample report

A more complete version of the example, with 27 Switch conditions, is provided with the sample report named Date Range Formula.rpt.

Sample reports are located in the Crystal Reports directory under \Samples\Reports.

When you preview the report, you supply a reference order date parameter, {?reference date} and a range condition, {?reference condition} to indicate a range of dates around the reference order date. The report is then previewed with only the records whose order dates fall in the specified range.

The entire record selection is performed on the database server. This is because the Switch function and all its arguments can be evaluated before accessing the database.

Note:

- In this sample report, the Switch function is returning a Date Range value.
- The indentation style of the code makes the Switch function call look similar to a control structure and makes reading the function easier.
- The last expression is the Boolean value True.

  This Switch function call provides for an explicit default value since if none of the other expression are True, the last expression is automatically True, and so the Date Range CDate (1899, 12, 30) To CDate(1899, 12, 30) is returned.

//Crystal syntax record selection formula
{Orders.Order Date} in
Switch
(
{?reference condition} = "Aged 0 to 30 days",
({?reference date} - 30) To {?reference date},
{?reference condition} = "Aged 31 to 60 days",
({?reference date} - 60) To ({?reference date} - 31),
{?reference condition} = "Aged 61 to 90 days",
({?reference date} - 90) To ({?reference date} - 61),
True, // provide default handling and specify a valid range
CDate(1899, 12, 30) To CDate(1899, 12, 30)
)

##### Comments

- This function is designed to work like the Visual Basic function of the same name. A difference is that the function does not return a Variant and thus all the values must have the same type.
- Every argument of the Switch function is evaluated before the result is returned. Thus, you should watch out for undesirable side effects when using Switch.

  For example, if one of the values results in division by zero, an error will occur, even if that value is not the value that is returned by the Switch function.

###### Related topics

Choose (index, choice1, choice2, ..., choiceN)

IIF (expression, truePart, falsePart)

.Select statements (Basic syntax)

Select expressions (Crystal syntax)

Seagate Software, Inc. http://www.seagatesoftware.com Please send comments to: techpubs@seagatesoftware.com