formula

[Srinivas]

i have designed a form in that there are two fields called actual payment
[AP] and
commission [comm] the scenario is like this if [AP] is from 1 to 25000 - the
bank will charge 25
as commision [comm] (THE FORMULA I WANT TO ENTER IN [COMM] and from 25001 to
50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60, 60001 to 75000, 75..........etc i want formula up
100000. But if nothing
there like 0 it should display "0"

Can any one help me please

 

[Wayne-I-M]

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

 

[John W. Vinson]

i have designed a form in that there are two fields called actual payment
[AP] and
commission [comm] the scenario is like this if [AP] is from 1 to 25000 - the
bank will charge 25
as commision [comm] (THE FORMULA I WANT TO ENTER IN [COMM] and from 25001 to
50000 - 50 then after 50001 to 55000 - 55 and
55001 to 60000 - 60, 60001 to 75000, 75..........etc i want formula up
100000. But if nothing
there like 0 it should display "0"

Can any one help me please

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.