I have a query which includes fields named openingbalance and commissions. I would like to compute values for commissions based on openingbalance, similar to this Select Case
block in Access VBA:
Select Case OpeningBalance
Case 0 To 5000
commission = 20
Case 5001 To 10000
commission = 30
Case 10001 To 20000
commission = 40
Case Else
commission = 50
End Select
But since Access doesn't allow Select Case
in a query, how can I accomplish my goal in Access SQL?
This question is related to
ms-access
select-case
You could do below:
select
iif ( OpeningBalance>=0 And OpeningBalance<=500 , 20,
iif ( OpeningBalance>=5001 And OpeningBalance<=10000 , 30,
iif ( OpeningBalance>=10001 And OpeningBalance<=20000 , 40,
50 ) ) ) as commission
from table
You can use IIF for a similar result.
Note that you can nest the IIF statements to handle multiple cases. There is an example here: http://forums.devshed.com/database-management-46/query-ms-access-iif-statement-multiple-conditions-358130.html
SELECT IIf([Combinaison] = "Mike", 12, IIf([Combinaison] = "Steve", 13)) As Answer
FROM MyTable;
Source: Stackoverflow.com