1. Registered User
Join Date
May 2004
Posts
17

I am somewhat new to Access. I am trying to create an IF/Then statement in a commission database that I created. The database has a field called [Profit Margin]. It has another one called [total Sales Price]. And lastly one called [IC Bonus]. The value of [IC Bonus] is based on the [Profit Margin] of the job. So if the [Profit Margin] is 36%, then multiply the [Total Sales Price] by .003. However, if the [profit margin] is between 37 and 39%, then multiply the [Total Sales Price] by .004. Can anyone help me with this?

2. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
Create a function in VBA to accomplish this calculation for you, i will bet there is more to this calculation than you are saying....

Regards

P.S. Why do you store this calculated value? Normalization rules say you shouldnt... store calculated values...

3. Registered User
Join Date
May 2004
Posts
17
Do you have an example of how to create this function? Your help will be much appreciated.

This is what I have so far, however it doesn't work.

If ([total profit margin] = 35 - 36) Then
[IC Bonus] = [total sales price] * 0.003
If ([total profit margin] = 37 - 38) Then
[IC Bonus] = [total sales price] * 0.004
If ([total profit margin] = 39 - 40) Then
[IC Bonus] = [total sales price] * 0.005
If ([total profit margin] = 41 - 42) Then
[IC Bonus] = [total sales price] * 0.006
If ([total profit margin] = 43 - 44) Then
[IC Bonus] = [total sales price] * 0.007
If ([total profit margin] = 44 - 45) Then
[IC Bonus] = [total sales price] * 0.008
If ([total profit margin] = 46 - 47) Then
[IC Bonus] = [total sales price] * 0.009
If ([total profit margin] >= 49) Then
[IC Bonus] = [total sales price] * 0.01

End If
Last edited by jor47; 05-19-04 at 08:38.

4. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
Originally Posted by jor47
I am somewhat new to Access. I am trying to create an IF/Then statement in a commission database that I created. The database has a field called [Profit Margin]. It has another one called [total Sales Price]. And lastly one called [IC Bonus]. The value of [IC Bonus] is based on the [Profit Margin] of the job. So if the [Profit Margin] is 36%, then multiply the [Total Sales Price] by .003. However, if the [profit margin] is between 37 and 39%, then multiply the [Total Sales Price] by .004. Can anyone help me with this?
IIf([Profit Margin] Like .36,[Total Sales Price]*.003,IIf([Profit Margin]>=.37 And [Profit Margin] <=.39),[Total Sales Price]*.004))

5. Registered User
Join Date
Apr 2004
Location
Sydney Australia
Posts
369
If ([total profit margin] = 41 - 42) Then
[IC Bonus] = [total sales price] * 0.006
If ([total profit margin] = 43 - 44) Then
[IC Bonus] = [total sales price] * 0.007

If you have trouble with code then this is very easy to do with a SetValue macro action. Assuming you are on a form then:

With the SetVale the

Item Line....will be [Forms]![YourFormName]![IC Bonus]

Expression line...will be [Forms]![YourFormname[Total Sales Price]*.006

In the condition colum you would have next to each SetValue action

[Forms]![YourFormName]![Total Profit Margin]>=41 And [Forms]![YourFormName]![Total Profit Margin]<=42

If the macro is run form the form YourFormName then you only need to include the field names both in the conditions and the Item and Expression.

In the scenario you have given you will have eight SetValue action lines.

Depending on your situation and the data base you might also want to consider storing in fields the various values you have such as the .006 and .007 and the 41 and 42 etc. If they are stored in fields then you replace the figures in the above actions, that is the .006 and 39, with the fields that contain that data.

If you do it that way then it means if decide to change your various bonus thresholds etc. then you change them in the fields and the macro (or code) will look after itself.

If you have your wits about you then you can avoid the conditions like
[Forms]![YourFormName]![Total Profit Margin]>=41 And [Forms]![YourFormName]![Total Profit Margin]<=42....that is the And part is removed and you let them run down the page

What happens is that is does the action for >=41. However say the value is 43 then it picks that up on another action line

Here ate some action lines in order as they appear from one that I use to calculate life insurance or disability premiums and in this case discounts on premium are given for the larger sum insured.

[L] Like "YR1BEN" And [CL Smoker] Like "Y" And [LSI]>=750000 And [MorF] Like "F"
[L] Like "YR1BEN" And [CL Smoker] Like "Y" And [LSI]>=1000000 And [MorF] Like "F"
[L] Like "YR1BEN" And [CL Smoker] Like "Y" And [LSI]>=2000000 And [MorF] Like "F"
[L] Like "YR1BEN" And [CL Smoker] Like "Y" And [LSI]>=5000000 And [MorF] Like "F"

Mike

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Hi

Namliam's suggestion is probably best otherwise you'll get yourself in knots.

create a new module and pop in:

Function fICBonus(sngProfMar As Single, sngSalesPrice As Single) As Single
'Singles may be an overkill but I don't know all your values. Also, your logic doesn't account for fractions - again don't know if it needed to.

Select Case sngProfMar

Case Is >= 49

fICBonus = sngSalesPrice * 0.01

Case Is >= 46

fICBonus = sngSalesPrice * 0.009

Case Is >= 44

fICBonus = sngSalesPrice * 0.008

'AND so on until

Case Is >= 35

fICBonus = sngSalesPrice * 0.003

Case Else

fICBonus = sngSalesPrice * 0.002 'or whatever value you chose
End Select

End Function

Then insert this expression into your query:

IC_Bonus: fICBonus([profitmargin],[salesprice])

Again, I would stress that you should be seeking to calculate this as you need it rather than store the information.

7. Registered User
Join Date
Jan 2004
Location
The Netherlands
Posts
421
Not exactly as i would do it, but it works.

Regards

8. Registered User
Join Date
May 2004
Posts
17
Thanks everyone. It works!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•