P: n/a

Hi,
I've got an invoice form that has a bunch of business rules behind it.
Basically, I'm trying to figure out how to enter a single number in a field
(e.g. $1000) and then have the form do a bunch of math on that number.
Here's the tough part. I need the form to breakdown the number based on a
tiered structure of business rules. For example, the rules might be:
On anything up to $300 take 1% in fees
Anything over $300 and less than $500 take .5% in fees
Anything $500 and over, take .25% in fees
Can anyone think of the most strategic way to do this on a single form in
order to come up with a single fee value? I'd appreciate any input.
Thanks! :)  
Share this Question
P: n/a

Select or If Then Else Statements might work, but there might be an easier way
to do it.
Michael  
P: n/a

There's 2 ways:
1) Use the IIf function and nest 2 or 3 levels of IIf
2) Write a VBA function
Although that fee structure you'd given might be an example, you will end up
getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25
You might want to consider a stepped fee structure where they pay 1% for the
first 300 and .5% on the next 200 and .25% on the rest e.g
Value 1% .5% .25% Total
$260.00 $2.60 $0.00 $0.00 $2.60
$440.00 $3.00 $0.70 $0.00 $3.70
$600.00 $3.00 $1.00 $0.25 $4.25
Stewart
"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02... Hi,
I've got an invoice form that has a bunch of business rules behind it. Basically, I'm trying to figure out how to enter a single number in a
field (e.g. $1000) and then have the form do a bunch of math on that number. Here's the tough part. I need the form to breakdown the number based on a tiered structure of business rules. For example, the rules might be:
On anything up to $300 take 1% in fees Anything over $300 and less than $500 take .5% in fees Anything $500 and over, take .25% in fees
Can anyone think of the most strategic way to do this on a single form in order to come up with a single fee value? I'd appreciate any input. Thanks! :)
 
P: n/a

Thanks Stewart.
Actually, the way it works is the whole amount gets charged a fee. So for
the $1000 example, I'd need the system to loop through and see that the
first $300 gets 1% which is $3. Then we have $700 remaining. The next tier
up to $500 is .5% so that would be $200 at .5% which is $1. Now we have
$500 remaining which hasn't been billed yet. That falls into the last tier
because we've already billed o the first $500. That last tier is .25% which
gets $1.25. Total fees for this customer is $5.25
Now, let's say customer B has $450. He gets same billing amount for the
first portion up to $300 which is $3. Now there is $150 remaining which
falls in the secondary tier of .5% so that is $150 x .5% which yields .75
cents. Total feeds for this customer is $3.75.
Basically, this is a pretty neat problem to crack. The logic is not bad. I
just don't know how to code this into a nested IIF statement. Not sure how
to tell the computer to move from one tier to the next. Any thoughts or an
example on how I could do this would be greatly appreciated.
Thanks a bunch!
"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz... There's 2 ways: 1) Use the IIf function and nest 2 or 3 levels of IIf 2) Write a VBA function
Although that fee structure you'd given might be an example, you will end
up getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 =
1.25 You might want to consider a stepped fee structure where they pay 1% for
the first 300 and .5% on the next 200 and .25% on the rest e.g Value 1% .5% .25% Total $260.00 $2.60 $0.00 $0.00 $2.60 $440.00 $3.00 $0.70 $0.00 $3.70 $600.00 $3.00 $1.00 $0.25 $4.25
Stewart
"MX1" <mx*@mx1.abc> wrote in message
news:JMAJc.87216$IQ4.34610@attbi_s02... Hi,
I've got an invoice form that has a bunch of business rules behind it. Basically, I'm trying to figure out how to enter a single number in a field (e.g. $1000) and then have the form do a bunch of math on that number. Here's the tough part. I need the form to breakdown the number based on
a tiered structure of business rules. For example, the rules might be:
On anything up to $300 take 1% in fees Anything over $300 and less than $500 take .5% in fees Anything $500 and over, take .25% in fees
Can anyone think of the most strategic way to do this on a single form
in order to come up with a single fee value? I'd appreciate any input. Thanks! :)
 
P: n/a

"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02... Hi,
I've got an invoice form that has a bunch of business rules behind it. Basically, I'm trying to figure out how to enter a single number in a
field (e.g. $1000) and then have the form do a bunch of math on that number. Here's the tough part. I need the form to breakdown the number based on a tiered structure of business rules. For example, the rules might be:
On anything up to $300 take 1% in fees Anything over $300 and less than $500 take .5% in fees Anything $500 and over, take .25% in fees
Can anyone think of the most strategic way to do this on a single form in order to come up with a single fee value? I'd appreciate any input. Thanks! :)
If you were to store your 'business rules' in a table you could probably
use a query to calculate and return a fee amount.  
P: n/a

Try (watch wrapping)
Fee:
CCur(IIf([Value]<=300,[Value]*0.01,IIf([Value]<=500,(([Value]300)*0.005)+3,
(([Value]500)*0.0025)+4)))
This is taken from a query and will work inside a text box on a form and
report.
The IIf function is a bit of resource hog especially when nexted. Here's a
function which does the same thing
Function Fee(Optional curIn As Currency) As Currency
Dim curTemp As Currency
Select Case curIn
Case 0
curTemp = 0
Case Is <= 300
curTemp = curIn * 0.01
Case Is <= 500
curTemp = ((curIn  300) * 0.005) + 3
Case Is > 500
curTemp = ((curIn  500) * 0.0025) + 4
End Select
Fee = curTemp
End Function
Stewart
"MX1" <mx*@mx1.abc> wrote in message news:nGCJc.85345$MB3.60308@attbi_s04... Thanks Stewart.
Actually, the way it works is the whole amount gets charged a fee. So for the $1000 example, I'd need the system to loop through and see that the first $300 gets 1% which is $3. Then we have $700 remaining. The next
tier up to $500 is .5% so that would be $200 at .5% which is $1. Now we have $500 remaining which hasn't been billed yet. That falls into the last
tier because we've already billed o the first $500. That last tier is .25%
which gets $1.25. Total fees for this customer is $5.25
Now, let's say customer B has $450. He gets same billing amount for the first portion up to $300 which is $3. Now there is $150 remaining which falls in the secondary tier of .5% so that is $150 x .5% which yields .75 cents. Total feeds for this customer is $3.75.
Basically, this is a pretty neat problem to crack. The logic is not bad.
I just don't know how to code this into a nested IIF statement. Not sure
how to tell the computer to move from one tier to the next. Any thoughts or
an example on how I could do this would be greatly appreciated.
Thanks a bunch! "Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message news:cd**********@news.wave.co.nz... There's 2 ways: 1) Use the IIf function and nest 2 or 3 levels of IIf 2) Write a VBA function
Although that fee structure you'd given might be an example, you will
end up getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25 You might want to consider a stepped fee structure where they pay 1% for the first 300 and .5% on the next 200 and .25% on the rest e.g Value 1% .5% .25% Total $260.00 $2.60 $0.00 $0.00 $2.60 $440.00 $3.00 $0.70 $0.00 $3.70 $600.00 $3.00 $1.00 $0.25 $4.25
Stewart
"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02... Hi,
I've got an invoice form that has a bunch of business rules behind it. Basically, I'm trying to figure out how to enter a single number in a field (e.g. $1000) and then have the form do a bunch of math on that number. Here's the tough part. I need the form to breakdown the number based
on a tiered structure of business rules. For example, the rules might be:
On anything up to $300 take 1% in fees Anything over $300 and less than $500 take .5% in fees Anything $500 and over, take .25% in fees
Can anyone think of the most strategic way to do this on a single form in order to come up with a single fee value? I'd appreciate any input. Thanks! :)
 
P: n/a

"MX1" <mx*@mx1.abc> wrote in
news:nGCJc.85345$MB3.60308@attbi_s04: Thanks Stewart.
Actually, the way it works is the whole amount gets charged a fee. So for the $1000 example, I'd need the system to loop through and see that the first $300 gets 1% which is $3. Then we have $700 remaining. The next tier up to $500 is .5% so that would be $200 at .5% which is $1. Now we have $500 remaining which hasn't been billed yet. That falls into the last tier because we've already billed o the first $500. That last tier is .25% which gets $1.25. Total fees for this customer is $5.25
Now, let's say customer B has $450. He gets same billing amount for the first portion up to $300 which is $3. Now there is $150 remaining which falls in the secondary tier of .5% so that is $150 x .5% which yields .75 cents. Total feeds for this customer is $3.75.
Basically, this is a pretty neat problem to crack. The logic is not bad. I just don't know how to code this into a nested IIF statement. Not sure how to tell the computer to move from one tier to the next. Any thoughts or an example on how I could do this would be greatly appreciated.
Thanks a bunch! "Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message news:cd**********@news.wave.co.nz... There's 2 ways: 1) Use the IIf function and nest 2 or 3 levels of IIf 2) Write a VBA function
Although that fee structure you'd given might be an example, you will end up getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25 You might want to consider a stepped fee structure where they pay 1% for the first 300 and .5% on the next 200 and .25% on the rest e.g Value 1% .5% .25% Total $260.00 $2.60 $0.00 $0.00 $2.60 $440.00 $3.00 $0.70 $0.00 $3.70 $600.00 $3.00 $1.00 $0.25 $4.25
Stewart
"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02... > Hi, > > I've got an invoice form that has a bunch of business rules > behind it. Basically, I'm trying to figure out how to enter > a single number in a field > (e.g. $1000) and then have the form do a bunch of math on > that number. Here's the tough part. I need the form to > breakdown the number based on a > tiered structure of business rules. For example, the rules > might be: > > On anything up to $300 take 1% in fees > Anything over $300 and less than $500 take .5% in fees > Anything $500 and over, take .25% in fees > > Can anyone think of the most strategic way to do this on a > single form in > order to come up with a single fee value? I'd appreciate > any input. Thanks! :) > >
What you need to do is build a table with your fee scale in it,
sorted DESCENDING.
CAP_Amount basefee MarginalRate
99999999 4.00 .0025
500 3.00 .005
300 0.00 .01
Now you can get the values by querying the TOP 1 row, where
CAP_Amount < invoice_Amount, and do ONE calculation and get the
correct fee:
BaseFee + (Invoice_Amount  CAP_Amount) * marginalRate.
You could also use separate dLookup()s to return the three values.
Bob Quintal  
P: n/a

Thanks. I will give this a shot.
"Bob Quintal" <bq******@generation.net> wrote in message
news:Xn**********************@66.150.105.50... "MX1" <mx*@mx1.abc> wrote in news:nGCJc.85345$MB3.60308@attbi_s04:
Thanks Stewart.
Actually, the way it works is the whole amount gets charged a fee. So for the $1000 example, I'd need the system to loop through and see that the first $300 gets 1% which is $3. Then we have $700 remaining. The next tier up to $500 is .5% so that would be $200 at .5% which is $1. Now we have $500 remaining which hasn't been billed yet. That falls into the last tier because we've already billed o the first $500. That last tier is .25% which gets $1.25. Total fees for this customer is $5.25
Now, let's say customer B has $450. He gets same billing amount for the first portion up to $300 which is $3. Now there is $150 remaining which falls in the secondary tier of .5% so that is $150 x .5% which yields .75 cents. Total feeds for this customer is $3.75.
Basically, this is a pretty neat problem to crack. The logic is not bad. I just don't know how to code this into a nested IIF statement. Not sure how to tell the computer to move from one tier to the next. Any thoughts or an example on how I could do this would be greatly appreciated.
Thanks a bunch! "Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message news:cd**********@news.wave.co.nz... There's 2 ways: 1) Use the IIf function and nest 2 or 3 levels of IIf 2) Write a VBA function
Although that fee structure you'd given might be an example, you will end up getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500 = 1.25 You might want to consider a stepped fee structure where they pay 1% for the first 300 and .5% on the next 200 and .25% on the rest e.g Value 1% .5% .25% Total $260.00 $2.60 $0.00 $0.00 $2.60 $440.00 $3.00 $0.70 $0.00 $3.70 $600.00 $3.00 $1.00 $0.25 $4.25
Stewart
"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02... > Hi, > > I've got an invoice form that has a bunch of business rules > behind it. Basically, I'm trying to figure out how to enter > a single number in a field > (e.g. $1000) and then have the form do a bunch of math on > that number. Here's the tough part. I need the form to > breakdown the number based on a > tiered structure of business rules. For example, the rules > might be: > > On anything up to $300 take 1% in fees > Anything over $300 and less than $500 take .5% in fees > Anything $500 and over, take .25% in fees > > Can anyone think of the most strategic way to do this on a > single form in > order to come up with a single fee value? I'd appreciate > any input. Thanks! :) > >
What you need to do is build a table with your fee scale in it, sorted DESCENDING.
CAP_Amount basefee MarginalRate 99999999 4.00 .0025 500 3.00 .005 300 0.00 .01
Now you can get the values by querying the TOP 1 row, where CAP_Amount < invoice_Amount, and do ONE calculation and get the correct fee:
BaseFee + (Invoice_Amount  CAP_Amount) * marginalRate.
You could also use separate dLookup()s to return the three values.
Bob Quintal  
P: n/a

Thanks. I'll give this a shot on Monday.
"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cd**********@news.wave.co.nz... Try (watch wrapping) Fee:
CCur(IIf([Value]<=300,[Value]*0.01,IIf([Value]<=500,(([Value]300)*0.005)+3, (([Value]500)*0.0025)+4))) This is taken from a query and will work inside a text box on a form and report.
The IIf function is a bit of resource hog especially when nexted. Here's a function which does the same thing
Function Fee(Optional curIn As Currency) As Currency Dim curTemp As Currency Select Case curIn Case 0 curTemp = 0 Case Is <= 300 curTemp = curIn * 0.01 Case Is <= 500 curTemp = ((curIn  300) * 0.005) + 3 Case Is > 500 curTemp = ((curIn  500) * 0.0025) + 4 End Select Fee = curTemp End Function
Stewart
"MX1" <mx*@mx1.abc> wrote in message
news:nGCJc.85345$MB3.60308@attbi_s04... Thanks Stewart.
Actually, the way it works is the whole amount gets charged a fee. So
for the $1000 example, I'd need the system to loop through and see that the first $300 gets 1% which is $3. Then we have $700 remaining. The next tier up to $500 is .5% so that would be $200 at .5% which is $1. Now we have $500 remaining which hasn't been billed yet. That falls into the last tier because we've already billed o the first $500. That last tier is .25% which gets $1.25. Total fees for this customer is $5.25
Now, let's say customer B has $450. He gets same billing amount for the first portion up to $300 which is $3. Now there is $150 remaining which falls in the secondary tier of .5% so that is $150 x .5% which yields
..75 cents. Total feeds for this customer is $3.75.
Basically, this is a pretty neat problem to crack. The logic is not
bad. I just don't know how to code this into a nested IIF statement. Not sure how to tell the computer to move from one tier to the next. Any thoughts or an example on how I could do this would be greatly appreciated.
Thanks a bunch! "Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message news:cd**********@news.wave.co.nz... There's 2 ways: 1) Use the IIf function and nest 2 or 3 levels of IIf 2) Write a VBA function
Although that fee structure you'd given might be an example, you will end up getting less in fees if they pay more e.g. 1% of 300 = 3, .25% of 500
= 1.25 You might want to consider a stepped fee structure where they pay 1%
for the first 300 and .5% on the next 200 and .25% on the rest e.g Value 1% .5% .25% Total $260.00 $2.60 $0.00 $0.00 $2.60 $440.00 $3.00 $0.70 $0.00 $3.70 $600.00 $3.00 $1.00 $0.25 $4.25
Stewart
"MX1" <mx*@mx1.abc> wrote in message news:JMAJc.87216$IQ4.34610@attbi_s02... > Hi, > > I've got an invoice form that has a bunch of business rules behind
it. > Basically, I'm trying to figure out how to enter a single number in
a field > (e.g. $1000) and then have the form do a bunch of math on that
number. > Here's the tough part. I need the form to breakdown the number
based on a > tiered structure of business rules. For example, the rules might
be: > > On anything up to $300 take 1% in fees > Anything over $300 and less than $500 take .5% in fees > Anything $500 and over, take .25% in fees > > Can anyone think of the most strategic way to do this on a single
form in > order to come up with a single fee value? I'd appreciate any input. > Thanks! :) > >
 
P: n/a

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in
news:bV******************@twister.nyroc.rr.com: If you were to store your 'business rules' in a table you could probably use a query to calculate and return a fee amount.
And it's exactly the kind of process that benefits from being
wrapped in a class module.

David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc   This discussion thread is closed Replies have been disabled for this discussion.   Question stats  viewed: 1235
 replies: 9
 date asked: Nov 13 '05
