Conditional Functions

Conditional functions let you evaluate different conditions and return a value based upon the result. These are very similar to the conditional structures If Then and Case because both allow you to evaluate different conditions and perform some action. However, conditional functions are different in they can evaluate all the conditions in one line of code and they return a value when finished. Conditional structures require multiple lines of code. If a conditional structure needs to return a value, it must do so by storing it in a temporary variable for use after it finishes executing. Since conditional functions return the value themselves, and don’t need a temporary variable, they can be used within another function! This gives you the ability to create a function that performs its operations using a number that changes depending upon certain conditions.

As an example, let’s say you have an If statement that evaluates a condition and returns the result. This number is later used in another calculation. To make this work there would have to be a function just for the If statement and this function would be called elsewhere in the report. You can replace this code with a single IIF() function (discussed next) that evaluates those conditions within the function and returns the value for use in the function. Everything is cleanly written with one line of code. An example that demonstrates how this works is within the discussion of the IIF() function.

Although conditional functions sound pretty good, they do have a drawback. If you get carried away with their use, your code will be harder to read and maintain. For example, an IIF() function is good at replacing a single IF statement. But if you want to replace a nested If statement, you will have to write nested IIF() functions. Although this will compile and run, it can be pretty hard for you or another programmer to read and understand. Use caution when deciding what is appropriate for the task at hand.

Being able to test conditions and return a result within a single function is very powerful. This section describes three conditional functions: IIF(), Choose(), and Switch().