IIF Statements

[JDA2005]

Anyone with some knowledge on how to build IIF statements through queries, your help is much appreciated.



Essentially what I'm trying to do is take this fee schedule among others...

0to $100,000,000*(0.3500&#37 $ = $ 350,000
$100,000,000 to $200,000,000* (0.3000%) $ = $ 300,000
$200,000,000 to $500,000,000* (0.2500%) $ = $ 750,000
$500,000,000 to $1,000,000,000* (0.1750%) $ = $ 875,000
$1,000,000,000 to 0.1200% $ -

So what I have no idea how to code is having this be calculated, then add the values so let's say the invesment is $300,000,000.

It would need to calculate the $350,000 + $300,000 then subtract 300,000,000 - 200,000,000 and multiply the remaining $100,000,000 by the 200-500 fee of 0.25%.

I hope I'm not over everyone's head here, its fairly straight forward but difficult to code into a query. All I need is an example of coding this or a db with similar calculations if anyone has, I'm a legit novice.

Thanks

[pdouglas]

Assuming you want to have someone paying full steps of commission (for say, 150M the commission on 100M plus the commission on the next 50M) there are several approaches.
Realizing the commission for the amounts below each threshold will stay the same, you just need to figure the commission for the amount above the threshold and add in the "Fixed" part
Iif(Amount <= 100M, Amount*0.0035, Iif(amount <= 200M, (Amount - 100M)*0.0030 + 350K, Iif(amount <= 500M, (amount - 200M)* 0.0025 + 650K, iif(amount <= 1000M,(amount - 500M)*0.00175 + 1400K, (amount - 1000M) * 0.0012 + 2250K))))
(Same as above, but you can see the logic. Use the above one.)
Iif(Amount <= 100M, Amount*0.0035,
Iif(Amount <= 200M, (Amount - 100M)*0.0030 + 350K,
Iif(Amount <= 500M, (Amount - 200M)* 0.0025 + 650K,
iif(Amount <= 1000M,(Amount - 500M)*0.00175 + 1400K,
(Amount - 1000M) * 0.0012 + 2250K))))

(I'm using 100M instead of typing out 100000000. You'll need to adjust that.)

Limitation on IIF - You can only have 6 nestled statements in there.

Option #2 - VBA Module
Click on Modules and copy/paste this in as a function
' From here
Public GetCommission(Amount as Currency) as Currency
On Error GoTo Err_Handler
Select Case Amount
case < 0
GetCommission = 0
case <= 100M
GetCommission = Amount * 0.0035
case 100M+0.01 to 200M
GetCommission = (Amount - 100M) * 0.0030 + 350K
case 200M + 0.01 to 500M
GetCommission = (Amount - 200M) * 0.025 + 650K
case 500M + 0.01 to 1,000M
GetCommission = (Amount - 500M) * 0.00175 + 1400K
case >1,000M
GetCommission = (Amount - 1000M) * 0.0012 + 2250K
End Select
ExitHere:
Exit Function
Err_Handler:
Select Case Err.Number
Case Else
MsgBox "Error - " & Err.Number & Chr(13) & Err.Description, vbOKOnly, "Error"
End Select
GoTo Exit_Here
End Function

' To Here
'----
To use it, have in your query
MyCommission:GetCommission(Amount)

[JDA2005]

Well done, thanks for the response.

I'll let you know how it goes.

[JDA2005]

OK, everything looks to be setup using your case method.

I'm I bit confused as to why you have error codes in there, is that just a prompt for incorrect amounts that are inputed?

Also I've never actually ran VBA before.

On one of my tables I have these commission schedules labeled as follows: 1.1, 1.2, 1.3.

How would I go about designing these modules to run all amounts that are on the same row as a given commission schedule? And in doing that, how do I setup the query to run these?

Thanks

[pdouglas]

Error Codes - That is what is called an "Error Handler" or "Exception Handler". This will give you a controlled way of handling things out of line. It is a generic one that you can copy/paste it into other VBA functions. You'll need to copy the 1st line (on err goto err_handler) and every thing from "ExitHere:" on down.

Running VBA doesn't hurt - Much

Third part about the Commission table. What I think you mean is you would like to be able to pull a cell of data out of a table. Assuming the table has some sort of ordering, you can use DLookup to find the value.
X = dlookup("[ValueYouWant]","[TableName]","[IndexOrOrder] = " & SomeValue)
You input the SomeValue and it will run off to the table and return the ValueYouWant that the SomeValue matches the IndexOrOrder.
I usually have these in a VBA function where I input one value and it returns what I want. If it doesn't find a match, then it will stomp its feet, wave its fists, and fall down to the Exception Handler. If it generates the one corresponding to no match, then I have the handler assign a Zero value as my result.

[JDA2005]

You're close, what I'm trying to pull is the values that are on the same row as the different schedules.

So I'm going to make various modules for these different schedules that have different rates for different values.

At some point I want to macro all these queries to be able to go down a table, locate their corresponding schedules and then compute the value through the modules that were created. Here's the table:



You can see the schedule, that has a value in it that is what the customer knows they will be charged at that rate. It is also in a row with the value that needs to be calculated using that specific schedule, refer to my 1st post for an example of the schedule.

I'll keep trying to Google the stuff you pointed out, but I'm still lost on how to actually use these modules effectively.

Greatly appreciate your time,

John

[pdouglas]

The image I got only showed the column headers, no data.
I prefer to have an Integer / Long as the Primary Key. Call it, for instance, NameID and have it be an Autonumber. Then there will only be one instance of it, regardless of however many "Smith, John" there are.
If you can narrow down things to return the NameID, then you can use the DLookup to look up each field you want.
With "SampleID" to be the one you're looking for,
Alpha = dlookup("[Market_Value]","[TableName]","[NameID] = " & SampleID)
Beta = dlookup("[Fee]","[TableName]","[NameID] = " & SampleID)
Gamma = dlookup("[Schedule]","[TableName]","[NameID] = " & SampleID)

On and on.

[JDA2005]

The Account_Code is the primary key, but is that necessary?

We're just trying use these modules, link them individually to a schedule then have a macro or a query run through the tables and have the modules calculate the values corresponding to the proper schedule.

Am I missing something cause you lost me with the dlookup method?

John

[JDA2005]

Here is what I have as an example, I stripped all the queries and other tables/forms because they were not needed for this.

Baisically the schedule on the far right, will indicate a unique module, that module needs to run for those market_values and calculate the fee.

Let me know what you guys think,

Thanks