Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    17

    Unanswered: If Then Statements

    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. #2
    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. #3
    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. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote 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. #5
    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. #6
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

    Regards

  8. #8
    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
  •