Hi,
In SQL, we do not say what to do but what to get. The database engine
determines what to do, once it knows what to get, what to retrieve.
SELECT iif( condition, thisIfTrue, thatIfFalseOrNull ) FROM ....
would instruct SQL to retrieve thisIfTrue is the condition evaluates to
TRUE, otherwise, retrieve the third argument (which can be an expression).
If you have many conditions, you can try a SWITCH:
SELECT SWITCH( condition1, value1, condition2, value2, ..., ..., TRUE,
DefaultValue) FROM ...
which instruct the database to return value1 if condition1 is true, else,
value2 if condition2 is true, else... until a condition that evaluates to
true, then, return its associated value.
If you use MS SQL Server, you would use CASE instead:
SELECT CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ...
ELSE DefaultValue END FROM ...
which is a little bit harder to understand, first time we see it, but
compare it to the SWITCH statement, and you should see that the punctuation
of the SWITCH is simply replaced with some fancy phrasing....
Hoping it may help,
Vanderghast, Access MVP