Show Table of Contents
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
it to a sequence of values,
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.
[Region] WHEN “West” THEN 1 WHEN “East” THEN 2 ELSE 3 END
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
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
then value. If
test evaluates to FALSE, then IIF returns
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.
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
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.
IF [Cost]>[Budget Cost]
THEN “Over Budget” ELSE “Under Budget” END
IF [Budget Sales]!=0 THEN
[Sales]/[Budget Sales] 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).
IF [Region]=”West” THEN
1 ELSEIF [Region]=”East” THEN 2 ELSE 3 END
The IFNULL function returns the first expression if the result is not null, and returns the second expression if it is null.
IFNULL([Proft], 0) = [Profit]
The ISDATE function returns
the string argument can be converted to a date and
FALSE if it cannot.
ISDATE("January 1, 2003") = TRUE
1 2003") = TRUE
ISDATE("1/1/03") = TRUE
1 2003") = FALSE
The ISNULL function returns
the expression is
FALSE if it is not.
The MIN function returns the minimum of an expression across all records or the minimum of two expressions for each record.