Home > 

Reference > Functions, Operators, & Data Types > Functions > Logical Functions

Logical Functions

CASE expression WHEN value1 THEN return1 WHEN value2 THEN return2...ELSE default return END

Use the CASE function to perform logical tests and return appropriate values. CASE is often easier to use than IIF or IF THEN ELSE. The CASE function evaluates expression, compares it to a sequence of values, value1, value2, etc., and returns a result. When a value that matches expression is encountered, CASE returns the corresponding return value. If no match is found, the default return expression is used. If there is no default return and no values match, then Null is returned.

Typically, you use an IF function to perform a sequence of arbitrary tests, and you use a CASE function to search for a match to an expression. But a CASE function can always be rewritten as an IF function , although the CASE function will generally be more concise.

Many times you can use a group to get the same results as a complicated case function.

Examples

CASE [Region] WHEN "West" THEN 1 WHEN "East" THEN 2 ELSE 3 END

CASE LEFT(DATENAME('weekday',[Order Date]),3) WHEN "Sun" THEN 0 WHEN "Mon" THEN 1 WHEN "Tue" THEN 2 WHEN "Wed" THEN 3 WHEN "Thu" THEN 4 WHEN "Fri" THEN 5 WHEN "Sat" THEN 6 END

If you need to include numeric comparisons in your conditions, use a nested IF clause. The CASE function compares strings only. For example, suppose you want to break the values of the Sales field into three custom categories: one for sales less than 200, one for sales between 200 and 300, and one for sales between 300 and 400. The formula would be:

IF [Sales] < 200 THEN "Low" ELSEIF [Sales] < 300 THEN "Medium" ELSEIF [Sales] < 400 THEN "High" ELSE "NULL" END

IIF(test, then, else, [unknown])

Use the IIF function to perform logical tests and return appropriate values. The first argument, test, must be a boolean: either a boolean field in the data source, or the result of a logical expression using operators (or a logical comparison of AND, OR, or NOT). If test evaluates to TRUE, then IIF returns the then value. If test evaluates to FALSE, then IIF returns the else value.

A boolean comparison may also yield the value UNKNOWN (neither TRUE nor FALSE), usually due to the presence of Null values in test. The final argument to IIF is returned in the event of an UNKNOWN result for the comparison. If this argument is left out, Null is returned.

Examples

IIF(7>5, "Seven is greater than five", "Seven is less than five")

IIF([Cost]>[Budget Cost], "Over Budget", "Under Budget")

IIF([Budget Sales]!=0,[Sales]/[Budget Sales],0)

IIF(Sales>=[Budget Sales], "Over Cost Budget and Over Sales Budget", "Over Cost Budget and Under Sales Budget"),"Under Cost Budget")

IF test THEN value END / IF test THEN value ELSE else END

Use the IF THEN ELSE function to perform logical tests and return appropriate values. The IF THEN ELSE function evaluates a sequence of test conditions and returns the value for the first condition that is true. If no condition is true, the ELSE value is returned. Each test must be a boolean: either be a boolean field in the data source or the result of a logical expression. The final ELSE is optional, but if it is not provided and there is no true test expression, then the function returns Null. All of the value expressions must be of the same type.

Examples

IF [Cost]>[Budget Cost] THEN "Over Budget" ELSE "Under Budget" END

IF [Budget Sales]!=0 THEN [Sales]/[Budget Sales] END

IF test1 THEN value1 ELSEIF test2 THEN value2 ELSE else END

Use this version of the IF function to perform logical tests recursively. There is no built-in limit to the number of ELSEIFvalues you can use with an IF function, though individual databases may impose a limit on IF function complexity. While an IF function can be rewritten as a series of nested IIF statements, there are differences in how the expressions will be evaluated. In particular, an IIF statement distinguishes TRUE, FALSE and UNKNOWN, whereas an IF statement only worries about TRUE and not true (which includes both FALSE and UNKNOWN).

Example

IF [Region]="West" THEN 1 ELSEIF [Region]="East" THEN 2 ELSE 3 END

IFNULL(expression1, expression2)

The IFNULL function returns the first expression if the result is not null, and returns the second expression if it is null.

Example

IFNULL([Proft], 0) = [Profit]

ISDATE(string)

The ISDATE function returns TRUE if the string argument can be converted to a date and FALSE if it cannot.

Examples

ISDATE("January 1, 2003") = TRUE

ISDATE("Jan 1 2003") = TRUE

ISDATE("1/1/03") = TRUE

ISDATE("Janxx 1 2003") = FALSE

ISNULL(expression)

The ISNULL function returns TRUE if the expression is Null and FALSE if it is not.

MIN(expression) or MIN(expression1,expression2)

The MIN function returns the minimum of an expression across all records or the minimum of two expressions for each record.