Complicated Calculations on forms

  • Thread starter Thread starter George
  • Start date Start date
G

George

Dear friends, need your valuable help again.

I am working on a Medical Fund database. I have a table where all benefits
info is kept, e.g. medicines, up to 300 Euros / year. Over 300 and up to
500, 50%, >500 – no money is refunded. Tables also for Employees, for
Invoices etc.

I have created a form with subform – Invoices and benefits (one invoice with
many employees / benefits). On the many form I have the BenefitID (e.g.
medicines), the employee no (is a unique value), the amount (as shown on the
invoice) and the EligibleAmount (should be automatically calculated based on
the above criteria). I have also put some textboxes on the subform which
show me (for the current employee) the total amount for the current benefit
per year – I believe I will need this to accomplish what I want to do.

Example for EmployeeA for medicines
1/1/2010 – 200 Euros – Eligible Amount = 200 Euros (yearly total = 200)
1/2/2010 – 200 Euros – Eligible Amount = 100 + (100*50/100) = 150 (yearly
totals = 350)
1/3/2010 – 200 Euros – Eligible Amount = 200 * 50/100 = 100 Euros (yearly
totals = 450)
1/4/2010 – 200 Euros – Eligible Amount = 50 (the rest amount up to 500)

Any ideas of how should I calculate, based on the above criteria, the
EligibleAmount?

Thanking you in advance,

GeorgeCY
 
hi George,

I am working on a Medical Fund database. I have a table where all benefits
info is kept, e.g. medicines, up to 300 Euros / year. Over 300 and up to
500, 50%,>500 – no money is refunded. Tables also for Employees, for
Invoices etc.
Example for EmployeeA for medicines
1/1/2010 – 200 Euros – Eligible Amount = 200 Euros (yearly total = 200)
1/2/2010 – 200 Euros – Eligible Amount = 100 + (100*50/100) = 150 (yearly
totals = 350)
1/3/2010 – 200 Euros – Eligible Amount = 200 * 50/100 = 100 Euros (yearly
totals = 450)
1/4/2010 – 200 Euros – Eligible Amount = 50 (the rest amount up to 500)

Any ideas of how should I calculate, based on the above criteria, the
EligibleAmount?
Click to expand...
Create a function like this in a standard module:

Public Function CalculateEligibleAmount( _
ADate As Date, AAmount As Money) As Money

CalculateEligibleAmount = 200

End Function

The problem is that I don't understand your formula.


mfG
--> stefan <--
 
The problem is that this drives me crazy :((

I have a benefit, e.g. medicines.

Each person can get up to 500 Euros per year (the first 300 euros are paid
100% and the rest 50% up to 500 euros). If someone gets 500 then he/she will
not be able to get more money for medicines.

Ο χÏήστης "Stefan Hoffmann" έγγÏαψε:
 
hi George,

Each person can get up to 500 Euros per year (the first 300 euros are paid
100% and the rest 50% up to 500 euros). If someone gets 500 then he/she will
not be able to get more money for medicines.
Click to expand...
I still don't get the calculation procedure. Can you give a concise and
complete description and example?


mfG
--> stefan <--
 
Thanks for the replies Stefan,

Here is the example - I need the Eligible Amount to be auto-calculated:

EmployeeA brings the following invoices to be paid (all are for medicines)

1) Date: 1/1/2010 – 200 Euros – Eligible Amount = 200 Euros (yearly total =
200)

2) Date: 1/2/2010 – 200 Euros – Eligible Amount = 100 + (100*50/100) = 150
(yearly totals = 350)

3) Date: 1/3/2010 – 200 Euros – Eligible Amount = 200 * 50/100 = 100 Euros
(yearly
totals = 450)

4) 1/4/2010 – 200 Euros – Eligible Amount = 50 (the rest amount up to 500)


Ο χÏήστης "Stefan Hoffmann" έγγÏαψε:
 
hi George,

This may sound harsh..

Thanks for the replies Stefan,
Click to expand...
This is maybe concise, but not complete.

- Post your actual table structure involved with some sample data.
- Post your desired result set for that sample data.
- Include more then one employee.
- Describe your calculation procedure carefully with simple words.
- Use an accurate formatting. I have really difficulties to read your
example without outlining it manually on paper.

...but give it a try.


mfG
--> stefan <--
 
George -

Here is the function you need. You will be passing in the amount of the
current medical amount and the benefits used to date:

Public Function CalculateEligibleAmount(MedAmt As Currency, EligBenToDate As
Currency) As Currency
' Eligible Benefits are maxed at 500 Euro
' First 300 Euro benefits paid at 100% up to 300 Euro
' Next 400 Euro paid at 50% for an additional 200 Euro (max payout per year
of 500 Euro)

Dim EligibleMedAmt As Currency
Dim EligibleAt50Pct As Currency
Dim ClaimedBenefitsTotal As Currency

'Calculate how much in expenses has been claimed (max of 700 Euro per
year claimed)
If EligBenToDate < 300 Then
ClaimedBenefitsTotal = EligBenToDate
Else
ClaimedBenefitsTotal = 300 + (EligBenToDate - 300)*2
End If

'Calculate the Eligible Expenses remaining (not the eligible benefit yet)
If MedAmt + ClaimedBenefitsTotal > 700 Then
EligibleMedAmt = 700 - ClaimedBenefitsTotal
Else
EligibleMedAmt = MedAmt
End If

'Calculate the remaining benefit amount
If EligibleMedAmt <= 0 Then 'Have used up benefits
CalculateEligibleAmount = 0
Else
If EligBenToDate >= 300 Then 'Pay remaining at 50%
CalculateEligibleAmount = EligibleMedAmt * 0.5
Else
If EligBenToDate + EligibleMedAmt <= 300 Then
CalculateEligibleAmount = EligibleMedAmt 'Pay at 100% because
total less than 300 Euro
Else 'Use up rest of 300 Euros at 100%, plus any spillover at 50%
CalculateEligibleAmount = (300 - EligBenToDate) +
(EligibleMedAmt - (300 - EligBenToDate)) * 0.5
End If
End If
End If

End Function
 
Thanks a lot for the answer Daryl,

I have the following 2 fields in my subform:
Amount - I will type this
EligibleAmount - I need this to be auto calculated using your function

Please help - how can I do this?

Using CalculateEligibleAmount() after update of the Amount field?

I have used

Ο χÏήστης "Daryl S" έγγÏαψε:
 
George -

This is how you would call the function and return the value to the
EligibleAmount control on your form:

Me.EligibleAmount = CalculateEligiibleAmount(Me.Amount,
Forms!parentFormName!EligibleAmountUsed)

Me.Amount refers to the name of the control on the current form that the
user enters the amount into. You will have to change the parentFormName to
be the name of the main form that holds the total eligible amount, and also
change the "EligibleAmountUsed" to be the name of the control that shows the
sum of the eligible amounts to date.
 
Thanks a billion dear Daryl.

It works perfectly :)))))))))

Ο χÏήστης "Daryl S" έγγÏαψε:
 
Happy to help!
Thanks,
--
Daryl S


George said:
Thanks a billion dear Daryl.

It works perfectly :)))))))))

Ο χÏήστης "Daryl S" έγγÏαψε:
Click to expand...
 
Back
Top