Case Statement in Access SQL

In Access, we can’t use CASE statement.

Alternatively, we can use Switch statement.

Usage

CASE x
WHEN a THEN p
WHEN b THEN q
WHEN c THEN r
ELSE y END

CASE x

WHEN a THEN p

WHEN b THEN q

WHEN c THEN r

ELSE y END

The above code was written with Switch as follows.

Switch(x=a, p, x=b, q, x=c, r, True, y)

1	Switch(x=a, p, x=b, q, x=c, r, True, y)

It was said that Access employs Switch because of compatibility with VBA, but I don’t know actual reason.

You can also use Iif. The following code leads same result.

iif(x=a, p, (iif(x=b, q, iif(x=c, r, y))))

1	iif(x=a, p, (iif(x=b, q, iif(x=c, r, y))))

It is a choice to use Choose, in some cases.

Choose(index, a, b, c, ...)

1	Choose(index, a, b, c, ...)

index に整数を入れると、index = 1 なら a が、 index = 2 なら b が返ります。整数でない場合は、一番近い整数に丸められます。 If index is less than 1, Null is returned.