Expression builder in a select query

K

Krish

I want to determine the qualifying % of commission based on Margin $ as follows
<74.99 0.00%
75 and <149.99 8%
150 and <249.99 10%
25 and <349.99 12%
400 14%
Click to expand...
My expression below do not produce correct results.I need help.
COMM_RATE:
IIf([Net_Margin]<74.99,"0.00",IIf([Net_Margin]<149.99,"8.00",IIf([Net_Margin]<224.99,"10.00",IIf([Net_Margin]<399.99,"12.00",IIf([Net_Margin]>400,"14.00",0)))))
 
J

John Spencer

COMM_RATE:
IIf([Net_Margin]<74.99,"0.00"
,IIf([Net_Margin]<149.99,"8.00"
,IIf([Net_Margin]<224.99,"10.00"
,IIf([Net_Margin]<399.99,"12.00"
,IIf([Net_Margin]>400,"14.00",0)))))

What results do you get and what results to you expect to get? Just saying
"My expression below do not produce correct results" does not help us trouble
shoot the problem.

I would change the expression slightly so that you don't miss some values
(74.99.149.99,224.99,399.99) and I would also return the numeric value of the
percentage - you can format it to display the way you wish.
COMM_RATE:
IIf([Net_Margin]<75,0
,IIf([Net_Margin]<150,.08
,IIf([Net_Margin]<225,.1
,IIf([Net_Margin]<400,.12
,IIf([Net_Margin]>=400,.14,0)))))


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I want to determine the qualifying % of commission based on Margin $ as follows
<74.99 0.00%
75 and <149.99 8%
150 and <249.99 10%
25 and <349.99 12%
400 14%
Click to expand...
My expression below do not produce correct results.I need help.
COMM_RATE:
IIf([Net_Margin]<74.99,"0.00",IIf([Net_Margin]<149.99,"8.00",IIf([Net_Margin]<224.99,"10.00",IIf([Net_Margin]<399.99,"12.00",IIf([Net_Margin]>400,"14.00",0)))))
Click to expand...
 
D

Duane Hookom

What results are you getting? We can't see them.

I would not do this in a complex expression in a query. The numbers and
ranges are going to change and you should not be searching for this
expression to make changes.

Your expression will return string values when I expect you want number
values. Don't put quotes around your return values.

You seem to want to return percent values like 0.08 which is 8%. You are
returning 8 which is 800%.

You are also missing values between 350 and 400.

These percentages and ranges should be stored in a small lookup table of
sales commissions. If you don't want to create a table, then consider
creating a small user-defined public function in a module of business rules.

Copy this function and paste it into a new module. Save the module with the
name "modBusinessCalcs". Update the comments

Public Function GetCommission(curMargin As Currency) As Double
'calculate the commission based on the Margin $
'business calculation as per ... on 1/5/2010
'author:
'This function is used ....
Select Case curMargin
Case Is < 75
GetCommission = 0
Case Is < 150
GetCommission = 0.08
Case Is < 250
GetCommission = 0.1
Case Is < 350
GetCommission = 0.12
Case Is < 400
GetCommission = 0.13
Case Else
GetCommission = 0.14
End Select
End Function

--
Duane Hookom
Microsoft Access MVP


Krish said:
I want to determine the qualifying % of commission based on Margin $ as follows
<74.99 0.00%
75 and <149.99 8%
150 and <249.99 10%
25 and <349.99 12%
400 14%
Click to expand...
My expression below do not produce correct results.I need help.
COMM_RATE:
IIf([Net_Margin]<74.99,"0.00",IIf([Net_Margin]<149.99,"8.00",IIf([Net_Margin]<224.99,"10.00",IIf([Net_Margin]<399.99,"12.00",IIf([Net_Margin]>400,"14.00",0)))))
Click to expand...
 
Top