Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Nested case select vba

    Hi - thanks for reading!!
    I need to make a change to some VBA that I've been running for a few years, shown below.
    It would compare CURRENCYCODE and add the assigned number to a column in my query based on the value of a INVAMT field.
    Code:
    Public Function ComputeCharges(ByVal CURRENCYCODE As Variant, INVAMT As Variant) As Variant
    
        If IsNull(CURRENCYCODE) Or IsNull(INVAMT) Then
            ComputeCharges = Null
        Else
            Select Case CURRENCYCODE
                Case "USD"
                    Select Case INVAMT
                        Case Is < 50:     ComputeCharges = 0
                        Case Is < 201:    ComputeCharges = 2
                        Case Is < 601:    ComputeCharges = 4
                        Case Is < 1001:   ComputeCharges = 6
                        Case Is < 2501:   ComputeCharges = 15
                        Case Is < 5001:   ComputeCharges = 30
                        Case Is < 10001:  ComputeCharges = 60
                        Case Is < 25001:  ComputeCharges = 90
                        Case Else:        ComputeCharges = 120
                    End Select
            
                Case "EUR"
                    Select Case INVAMT
                        Case Is < 40:      ComputeCharges = 0
                        Case Is < 201:     ComputeCharges = 2
                        Case Is < 501:     ComputeCharges = 3
                        Case Is < 801:     ComputeCharges = 5
                        Case Is < 1901:    ComputeCharges = 11
                        Case Is < 3801:    ComputeCharges = 23
                        Case Is < 7601:    ComputeCharges = 46
                        Case Is < 19001:   ComputeCharges = 69
                        Case Else:         ComputeCharges = 92
                     End Select
            
                Case "RMB"
                    Select Case INVAMT
                        Case Is < 300:      ComputeCharges = 0
                        Case Is < 1301:     ComputeCharges = 13
                        Case Is < 3801:     ComputeCharges = 25
                        Case Is < 6301:     ComputeCharges = 38
                        Case Is < 15901:    ComputeCharges = 95
                        Case Is < 31701:    ComputeCharges = 190
                        Case Is < 63501:    ComputeCharges = 381
                        Case Is < 158601:   ComputeCharges = 571
                        Case Else:          ComputeCharges = 761
                    End Select
                Case Else
                    ComputeCharges = Null
            End Select
        End If
    Now I need to add a variant INVDATE so that this ComputeCharges remains in my queries for INVDATE prior to 1/1/15.
    And for INVDATE greater than 12/31/14, I need ComputeCharges to be INVAMT x 1%

    Can anyone assist with how I would nest another level into this? It would need to loop like this
    (keeping the same as above but only for these dates) CASE 1: INVDATE <1/1/15
    CURRENCY CODE USD =
    Case INVAMT = ComputeCharge assigned
    CURRENCY CODE EUR =
    Case INVAMT = ComputeCharge assigned
    CURRENCY CODE RMB =
    Case INVAMT = ComputeCharge assigned

    CASE 2: INVDATE >12/31/14
    CURRENCYCODE USD =
    Case INVAMT = ComputeCharge INVAMT x 1% (or *.01)
    CURRENCYCODE EUR =
    Case INVAMT = ComputeCharge INVAMT x 1% (or *.01)
    CURRENCYCODE RMB =
    Case INVAMT = ComputeCharge INVAMT x 1% (or *.01)

    I'm hoping just to add INVDATE As Variant, and maybe re-write as a Case True Case False.
    My difficulty is proper looping and data types, and I can't get my head around how to make a calculation on the INVAMT in Case 2.
    Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    hard coding this sort of process is a recipe for problems in the future.
    I'd argue they should be in a rates table
    why?
    well what happens if say one of your customers is Greek, who knows in the near future they may be using the Drachma (again)
    what happens if a new customer comes along who doesn't use USD or EUR
    what happens if the business decides to have a special offer / process for one calendar month
    or say have a mad hour (where prices are dropped fro a specified time period....
    what happens if they decide to create a new bandingsay spend more than 48000 compoutecharges =....


    in your design each and everytime that happens yopu need to recode, retest, then deploy

    stuff it into a rates table (ie from 01 Jan 2015 USD %0 or less 0,...

    use a query to find the right rate AND the right value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Quote Originally Posted by healdem View Post
    hard coding this sort of process is a recipe for problems in the future.
    I'd argue they should be in a rates table
    why?
    well what happens if say one of your customers is Greek, who knows in the near future they may be using the Drachma (again)
    what happens if a new customer comes along who doesn't use USD or EUR
    what happens if the business decides to have a special offer / process for one calendar month
    or say have a mad hour (where prices are dropped fro a specified time period....
    what happens if they decide to create a new bandingsay spend more than 48000 compoutecharges =....


    in your design each and everytime that happens yopu need to recode, retest, then deploy

    stuff it into a rates table (ie from 01 Jan 2015 USD %0 or less 0,...

    use a query to find the right rate AND the right value
    Nice synopsis! This is genius!
    You've given me a new path - THANKS!

Posting Permissions

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