Search help for nested IIf's

Something like this

IIf ([AP]=#, [Comm] =#, IIf ([AP]=#,  [Comm] =#, IIf ([AP]=#,  [Comm] =#,
IIf ([AP]=#, ) ) ) ) etc, etc,etc

Note - you can have a maxium of 7 nested IIf's

Three solutions come to mind, in increasing order of preference:

1. Use nested IIF's as Wayne suggests. Simple to implement, but can be
difficult to maintain and will run out of steam if there are too many ranges.

2. Use the Switch() function. It takes pairs of arguments, and evaluates them
left to right; when it first encounters a pair with TRUE as the value of the
first element of the pair, it returns the second element and quits. So:

Commission: ([AP] <= 25000, 25, [AP] <= 50000, 50, [AP] <= 55000, 55, <etc up
to 100000>, True, 0)

The last pair catches those conditions where none of the stated conditions
apply (such as a NULL value of AP).

3. Best but most laborious solution: create a Commissions table with three
fields, Low, High and Commission. Each row would have the values you need. You
can create a "Non Equi Join" query:

SELECT yourtable.*, Commissions.Commision
FROM yourtable
INNER JOIN Commissions
ON yourtable.AP >= Commissions.Low
AND yourtable.AP <= Commissions.High;

This solution is best in many ways because you can edit the Commissions table
as needed, without having to delve into the complex IIF or Switch() statements
in your code - and without having to remember all of the places such
expressions might be hidden.