Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    ahoneycutt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    18

    Switch Statement and IIF statements are too long. What is my other option

    I am running a crosstab query and have created a new field. This new field needs to evaluate the condition of several of the crosstab columns and then produce an answer.

    I have ran out of space using a Switch command or IIf statement and was thinking I could create a Select Case situation but I have little VBA experience.

    Here is my what I need. (Short version)

    Fields in qry.
    Plan, JobID, EmployeeID,TotalSales,TotalBonus,BonusAmt(I need to create this field using this select statement)

    I need to create something similiar to this but do not know how to create the function and I am sure this code is wrong but hopefully you can get what I am trying to do.
    Public Function CalcBonus() As Currency
    Dim ? as Integer
    ? = ?



    Select Case ?
    Case 1
    [Plan]=1 and [TotalSales]>=50000
    NewField(BonusAmt) = $500.00

    Case 2
    [Plan] = 2 and [TotalSales]>=60000
    NewField(BonusAmt) = $750.00

    Case Else
    Newfield(BonusAmt) = 0

    End Case

    There are at least 15 case possibilites and there are other fields that will be evaluated in the case statement but those I have working fine in the switch statements that I have. The only problem is its too long.

    Once I have the code working, I suppose in the qry that I am running I will create the newfield and it will call this function? Maybe, BonusAmt:CalcBonus()

    Please help. I need this done today if at all possible.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You could maybe create a table of these bonus amounts and their attributes (thresholds and plans). Then a domain aggregate could pull the correct bonus amount based on TotalSales and Plan values. Something like:

    =DMax("Bonus", "BonusTable", "Threshold <= " & [TotalSales] & " AND Plan =" & [Plan])

    If bonus amounts and thresholds can be changed over time, probably should be a table anyway for easier updating, otherwise major change to code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You would want something closer to

    Dim intMyInt as Integer

    intMyInt = Me.Somefield

    Select Case intMyInt
    Case 5002
    'Do the 5002 thing
    Case 5003
    NewField(BonusAmt) = $750.00

    End Select

    I have a video tutorial series for Intro to VBA. One of the videos covers Select Case Statements. You might want to check it out.
    https://www.accessforums.net/tutoria...ers-52741.html

  4. #4
    ahoneycutt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    18
    Can the intMyInt = Me.Somefield be a field that is only being created on when the qry runs? Like my BonusAmt field. The Newfield(BonusAmt) is the field I am creating when the qry runs. So depending on the case I want that field to have the results of the true case.

    This qry pulls from a crosstab and another query so the field I want to feed based on the cases will be a field that doesn't exist anywhere yet.

    Thanks so much for the help. Sorry I am great at Access but horrible at VBA.

    How do I call the function in the query?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Did you consider table suggestion in post 2?

    If the bonus changes over time, how will you make sure the correct bonus is associated with existing older records? If you need to be able to replicate this output for any period at any time, then need to store the bonus data somewhere. Or else include a time element in the conditional and a much longer VBA procedure. A table can have a time stamp for each record. And the DMax can consider that as criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by ahoneycutt View Post
    ...Can the intMyInt = Me.Somefield be a field that is only being created on when the qry runs?...
    The short answer is yes. But, like June mentioned, now you are storing calculated data. And, to make matters worse, there is not an audit trail because the formula could change and there is not a record of it.

  7. #7
    ahoneycutt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    18
    The bonus is set based on other fields. Here is a better example of the code. It was a work in progress so that's why I didn't post it all at first. It's really not the bonus amount I am calculating its the bonus %.

    Public Function CalcBonus() As Currency
    Dim intMyInt As Integer
    intMyInt = Me.BonusAmt (What field name should this be?)
    Select Case intMyInt
    Case 1
    [Plan] = 1 And [TotalBonus] >= [50%_Min%]
    [BonusAmt] = [TotalBonus]

    Case 2
    [Plan] = 1 And [EB] >= [50%_Thres] And [EB] <= [50%_Max] And [TotalBonus] <= [50%_Min%] And [EB] > [30%_Min%]
    [BonusAmt] = [50%_Min%]
    Case 3
    [Plan] = 1 And [EB] >= [30%_Thres] And [EB] <= [30%_Max] And [TotalBonus] >= [30%_Min%]
    [BonusAmt] = [30%_Min%]

    'Evaluate Plan 2
    Case 4
    [Plan] = 2 And [TotalBonus] >= [50%_Min%]
    [BonusAmt] = [TotalBonus]
    Case 5
    [Plan] = 2 And [Direct Control] >= [50%_Thres] And [Direct Control] <= [50%_Max] And [TotalBonus] <= [50%_Min%] And [Direct Control] > [30%_Min%]
    [BonusAmt] = [50%_Min%]
    Case 6
    [Plan] = 2 And [Direct Control] >= [30%_Thres] And [Direct Control] <= [30%_Max] And [TotalBonus] >= [30%_Min%]
    [BonusAmt] = [30%_Min%]
    Case Else
    [BonusAmt] = "0"


    Hope this makes more since. I think I am close but I just don't know how to call it form the qry and what needs to go at the top.

  8. #8
    ahoneycutt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    18
    Thanks so much and I agree but this amount is a forecast amount and will change going forward. Once its the final amount then I will store it in a make table but for now it needs to change but its only a "what if" scenario.

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I do not understand Case 1, Case 2, Case 3, etc. Is Me.BonusAmt ever going to equal 1 or equal 2?

    So, you cannot do it this way. One thing you could do is create a function that calculates bonuses. Maybe have two functions, one working with the other in cases where additional calculations/evaluations are necessary. Then something like ...

    Dim dblBonus as Double
    dblBonus = 0.0


    Select Case Me![Plan].value

    Case 1
    dblBonus= myPlan1Function

    Case 2
    dblBonus = myPlan2Function

    Case 3
    dblBonus = myPlan3Function


    Case Else
    'There was an error
    'TODO create an error trap

    End Select

    msgbox dblBonus

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    To call in a query, the function needs parameters passed to it by argument. The function cannot reference the table and field names directly (this would require a recordset object using table as source). Following example passes one parameter, add as many as needed to do the calcs.

    Public Function CalcBonus(intPlan As Integer {variable2}, {variable3}, etc) As Currency
    Select Case intPlan

    Now call the function from query on the 'Field' row:

    BonusCalc: CalcBonus([Plan], [another field], [and another field], etc.)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    ahoneycutt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    18
    I guess I was thinking about this all wrong. I'm so sorry to take up your time.

    I orginally had an iif statement that worked great for plan one and plan 2 but it was too long to continue. So is the Switch command. After reading online I thought of the case option but as you stated this is not going to work this way.

    So I will ask it a different way. Below, was my switch command. How can I accomplish the same result with VBA but have more Plan's included since both Switch and IIf are not long enough. I basically need to calculate the BonusAmt field based on these statements and there are at least 17 possible plans.

    BonusAmt: Switch([Plan]=1 And [TotalBonus]>=[50%_Min%],[TotalBonus],[Plan]=1 And [EB]>=[50%_Thres] And [EB]<=[50%_Max] And [TotalBonus]<=[50%_Min%] And [EB]>[30%_Min%],[50%_Min%],[Plan]=1 And [EB]>=[30%_Thres] And [EB]<=[30%_Max] And [TotalBonus]>=[30%_Min%],[30%_Min%],[Plan]=2 And [TotalBonus]>=[50%_Min%],[TotalBonus],[Plan]=2 And [Direct Control]>=[50%_Thres] And [Direct Control]<=[50%_Max] And [TotalBonus]<=[50%_Min%] And [Direct Control]>[30%_Min%],[50%_Min%],[Plan]=2 And [Direct Control]>=[30%_Thres] And [Direct Control]<=[30%_Max] And [TotalBonus]>=[30%_Min%],[30%_Min%],0)

    Is what you just suggested the best method? Keep in mind I do not know VBA very good.

    Thanks again for your patience and help.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Options:

    1. the expanded function as described and call function from query or textbox

    2. build a table and pull value from table by domain aggregate function
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by June7 View Post
    Options:

    1. the expanded function as described and call function from query or textbox

    2. build a table and pull value from table by domain aggregate function
    I agree ...

  14. #14
    ahoneycutt is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Posts
    18
    Quote Originally Posted by ItsMe View Post
    I do not understand Case 1, Case 2, Case 3, etc. Is Me.BonusAmt ever going to equal 1 or equal 2?

    So, you cannot do it this way. One thing you could do is create a function that calculates bonuses. Maybe have two functions, one working with the other in cases where additional calculations/evaluations are necessary. Then something like ...

    Dim dblBonus as Double
    dblBonus = 0.0


    Select Case Me![Plan].value

    Case 1
    dblBonus= myPlan1Function

    Case 2
    dblBonus = myPlan2Function

    Case 3
    dblBonus = myPlan3Function


    Case Else
    'There was an error
    'TODO create an error trap

    End Select

    msgbox dblBonus
    What would the MyPlan#Function look like?

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I think you better go with a table and domain aggregate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Too long of query, way to store statements?
    By btappan in forum Queries
    Replies: 3
    Last Post: 12-15-2013, 10:12 PM
  2. Switch Statement
    By comfygringo in forum Queries
    Replies: 4
    Last Post: 09-04-2013, 03:07 PM
  3. Option Button If-Then statements
    By dccjr in forum Programming
    Replies: 5
    Last Post: 01-07-2013, 08:06 PM
  4. Switch Statement in WHERE Clause
    By Gray in forum Queries
    Replies: 1
    Last Post: 06-02-2011, 06:50 AM
  5. Replies: 6
    Last Post: 04-06-2010, 03:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums