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! :) 9 1431
Select or If Then Else Statements might work, but there might be an easier way
to do it.
Michael
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! :)
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! :)
"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.
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! :)
"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
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
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! :) > >
"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 thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: qurat |
last post by:
Alachisoft TierDeveloper is an O/R mapping and code generation tool that helps software developers do better, more creative and useful work by reducing redundant hand coding. TierDeveloper lets you...
|
by: NoEd |
last post by:
I was talking to a couple guys at one of the hospitals I consult with, and
they indicated their 401K is charging them $8 per quarter and .0875% based
on the value in their accounts at the end of...
|
by: thomas.porschberg |
last post by:
Hi,
I want to read records from a database and export it in an arbitrary
format.
My idea was to feed a class with a String array fetched from the
database and let
this class fire SAX events as...
|
by: zeeshansohail |
last post by:
I have two tables named as “COMPANY and BILL_DETAIL” with the following structure.
BILL_DETAIL TABLE
Name Type Nullable
------------- -------------- -------- ...
|
by: assgar |
last post by:
Hi
I need help solving a porblem.
I have a form that displays a checkbox, service code, description
and dropdown with fees on each row.
The fee_money and unit array only returns a...
|
by: smacky61 |
last post by:
How do I summarize a report by finding the totals fees collected for each event and the grand total of all fees. The name of the report is Registered Attendees Report.
|
by: Johannes Nix |
last post by:
Hi,
this might be of interest for people who are look for practical
information on
doing real-time signal processing, possibly using multiple CPUs, and
wonder
whether it's possible to use...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |