Consider the Switch Function as an alternative to multiple IIf()
expressions. It will return the value from the first expression/value pair where the expression evaluates as True, and ignore any remaining pairs. The concept is similar to the SELECT ... CASE
approach you referenced but which is not available in Access SQL.
If you want to display a calculated field as commission
:
SELECT
Switch(
OpeningBalance < 5001, 20,
OpeningBalance < 10001, 30,
OpeningBalance < 20001, 40,
OpeningBalance >= 20001, 50
) AS commission
FROM YourTable;
If you want to store that calculated value to a field named commission
:
UPDATE YourTable
SET commission =
Switch(
OpeningBalance < 5001, 20,
OpeningBalance < 10001, 30,
OpeningBalance < 20001, 40,
OpeningBalance >= 20001, 50
);
Either way, see whether you find Switch()
easier to understand and manage. Multiple IIf()s
can become mind-boggling as the number of conditions grows.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…