# Thread: Outgrew Iif And - what is alternative? Access 07

1. Registered User
Join Date
Jun 2010
Posts
189

## 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. Moderator
Join Date
Mar 2009
Posts
5,442
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]))`

3. Registered User
Join Date
Jun 2010
Posts
189
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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```

5. Moderator
Join Date
Mar 2009
Posts
5,442
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```

6. Registered User
Join Date
Jun 2010
Posts
189
Sinndho -

** TRIUMPH **

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

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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

8. Moderator
Join Date
Mar 2009
Posts
5,442
You're welcome!

9. Registered User
Join Date
Jun 2010
Posts
189
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
•