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

    Unanswered: Outgrew Iif And - what is alternative? Access 07

    I needed a way to determine what the companies service charge on a customer invoice is based on 2 criteria.....this company has independent sales reps so they are charged a fee for the invoices they put through
    If it is an invoice for a freight charge (shown in table as FRTINV = 1) - there is no order charge
    If it is not an invoice for freight (shown in table as FRTINV =0 - then the charge is variable based on INVAMT
    I have been running this expression in my invoices query which returned the correct results:
    1. IIf([FRTINV]=1,0,
    2. IIf([FRTINV]=0 And [INVAMT]<50,0,
    3. IIf([FRTINV]=0 And [INVAMT]<201,2,
    4. IIf([FRTINV]=0 And [INVAMT]<601,4,
    5. IIf([FRTINV]=0 And [INVAMT]<1001,6,
    6. IIf([FRTINV]=0 And [INVAMT]<2501,15,
    7. IIf([FRTINV]=0 And [INVAMT]<5001,30,
    8. IIf([FRTINV]=0 And [INVAMT]<10001,60,
    9. IIf([FRTINV]=0 And [INVAMT]<25001,90,120)))))))))

    the problem I have now is the table has changed whereas I have invoices in different currencies and the resulting order charges are different.
    I am trying to limit queries in this database for efficiency

    so for me to get accurate order charges now i have to add another criteria to each of these 9 lines where CURRENCY = "USD", then continue on with more Iif lines for each set of currencies(total of 3).
    I am unable to get the 3 criteria to work for USD - I tried this:
    IIf(([FRTINV]=0) And ([CURRENCY]=”USD”) And ([INVAMT]<50),0),
    etc etc
    It can only return 0 , or 120 so I know something in the structure is incorrect.

    and if I get the structure correct, can I add 32 more Iif lines? It doesn't seem efficient

    the hierarchy to this is:
    1.) is it freight invoice? YES = no order charge enter 0.00, done
    NO = it has a order charge, continue
    2.) what is the currency? USD = US Order Charges
    EUR = EUR Order Charges
    RMB = RMB Order Charges
    3.) what is the invoice amount?

    If I create cases for the order charges how do I start?
    First I want to yes the invoices that are not for freight
    CASE FRT
    [FRTINV] = 1
    CASE ORDERCHARGE
    [FRTINV] =0
    Then another case for all ORDERCHARGE invoices?
    do I start with the charge or with the invoice? such as:
    CASE 0
    "USD"<50 and "RMB"<300 and "EUR"<40
    *this is saying that the order charge is 0 if the invoices are.......
    CASE 1
    "USD" <50 = 0
    *this is saying my first case is a USD invoice under 50 is 0
    which way will VBA want to read this?
    from there - how do I pull the case into the query to get the result?

    Appreciate any help or insight you can provide!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would use a public VBA function that would be called from the query:
    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
                Case "EUR"
                    ' Repeat the Select Case InvAmt for "EUR"
                Case "RMB"
                    ' Repeat the Select Case InvAmt for "RMB"
                Case Else
                    ComputeCharges = Null
            End Select
        End If
        
    End Function
    Then, in the query:
    Code:
    IIf([FRTINV]=1, 0, ComputeCharges([CURRENCY], [INVAMT]))
    Have a nice day!

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    Thank You Sinndho - this is exactly the direction I want to go!
    I wrote the function into VBA and I kept getting compile errors on End blocks.
    It required me to enter 7 End Selects to mtch to each Case Select like this:

    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
    Select Case CurrencyCode
    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
    Select Case CurrencyCode
    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
    Select Case CurrencyCode
    Case Else
    ComputeCharges = Null
    End Select
    End Select
    End Select
    End Select
    End If


    End Function

    Problem is in the query it is only running USD, which I'm assuming i because this code is ending on that first End Select. EUR and RMB are coming up blank.
    If I change the invamt to a case instead of selectcase the comile error is Case without Case Select......
    How do I get the CaseSelect to continue running after the first criteria USD is not met?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Consider creating another table in your database that has your criteria in separate columns alongside the desired result.

    You can then JOIN to this table in SQL. Something like this [air-code]:
    Code:
    SELECT criteria.result
         , your_table.*
    FROM   your_table
     LEFT
      JOIN criteria
        ON criteria.frtinv = your_table.frtinv
       AND criteria.currency = your_table.currency
       AND criteria.lower_invamnt <= your_table.invamt
       AND criteria.upper_invamnt >  your_table.invamnt
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    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
    
    End Function
    Have a nice day!

  6. #6
    Join Date
    Jun 2010
    Posts
    186
    Sinndho -

    ** TRIUMPH **

    I appreaciate your help so much! thank you!
    Genius!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    writing the rules using a table, as Gvee has suggested, is going to be a smarter call in the medium to long term. although it will take moire developer resource in the short term. the one thing you can guarantee with this busienss requirement is that soemday, some clever dick, sorry accoutant will want to fiddel with the rules. hard coding using IF statements is fine, except that if they want to change soemthing it requires developer time. devisng these rules using tables means it requires no developer maintenance over time. the cleverdick, sorry accountant can change what they like when they like AND carrythe can if its wrong
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Jun 2010
    Posts
    186
    healdem and gvee - interesting perspective........very worthy to consider

    where this program is designed on the sales rep level, it's for their commission analysis which is their only bread and butter.....those charges won't change or need to be altered

    I need to do some research on the tables idea......I'm trying to figure out how to loop the data back and forth
    main table, new table, query to result

    I appreciate the input!

Posting Permissions

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