Distribution of the amount (1 Viewer)

arnelgp said:

what is the risk?


sorry mr.waleed, cannot put the code to
your db, it must be you are using Arabic
Office.
anyway put LongContracts Form in Design View.
on the Click Event of "Distribution of the Amount",
add this code:


'this is hard-coded
'since we already know the calculation
Dim db As Dao.Database
Set db=Currentdb
'January
db.Execute "Insert Into AmountsDistributed (No,DateOperation,AmountDistribution) " & _
"SELECT " & Me.Parent![Project No] & "," & _
DateSerial(2017,1,10) & "," & _
2222

'February
db.Execute "Insert Into AmountsDistributed (No,DateOperation,AmountDistribution) " & _
"SELECT " & Me.Parent![Project No] & "," & _
DateSerial(2017,2,28) & "," & _
3333
'March
db.Execute "Insert Into AmountsDistributed (No,DateOperation,AmountDistribution) " & _
"SELECT " & Me.Parent![Project No] & "," & _
DateSerial(2017,3,31) & "," & _
3333
'April
db.Execute "Insert Into AmountsDistributed (No,DateOperation,AmountDistribution) " & _
"SELECT " & Me.Parent![Project No] & "," & _
DateSerial(2017,4,10) & "," & _
1112
Me.Requery

Click to expand...

llkhoutx said:

I would never open a posted database unless I knew the posted. Anyone that opens it is taking a significant risk.

Click to expand...

Before opening any user database, I, for one, ALWAYS put on full body armour and enclose my entire PC in a Faraday cage .....

waleed alesayi said:

I have a program to distribute the amounts according to the duration of the contract Please kindly help when you press the button distribution of the amount in the sub-form, knowing that the amounts are not necessarily equal to the beginning of the contract from 201/7/1/10 until 2017/4/10, The amount will be:

First month of January in the date of 2017/1/10 and in the amount of the amount the following equation is the value of the contract divided by the duration of the contract (3 months) divided by 30 days of the month (on the basis of the month fixed 30 days) multiplied by 20 days to complete the month the amount 2222 .

Second: - The month of February in the date box shall be 2017/2/28 and in the amount of the distribution of the whole month the value of the contract divided by 3 shall be the amount 3333.

Second: - The month of March in the date box will be 2017/3/31 and in the amount of the distribution of the entire month the value of the contract divided by 3 shall be 3333.

Third: The month of April in the date box 2017/4/10 and in the amount of the amount the following equation is the value of the contract divided by the contract period (3 months) divided by 30 days of the month (based on the month fixed 30 days) multiplied by 10 days to complete the month shall be Amount 1111.

This is similar to the premium program, but the distribution of the sum is not equal.
Thank you

Click to expand...

Solo712 said:

This is not sufficient info to create a generalized formula.

1)Are you seeking a distribution formula for "x" currency where the project has "n" month duration ?

2) Is the month always "a boundary", i.e. if a contract starts on the 15th of one month, it always ends on the 14th of the final month?

In case month is not a boundary, could you show us, e.g. how you would expect 170,000 (your currency) to be distributed over 127 days beginning on the 15th of January ?

3)If Months are standardized at 30 days what is the rounding process ? Do you distribute on the basis of 10-day increments ? Can the contract start on any day of the month?


Best,
Jiri

Click to expand...

ridders said:

Before opening any user database, I, for one, ALWAYS put on full body armour and enclose my entire PC in a Faraday cage .....

But actually, if I'm at all suspicious that it might cause issues, I will open user databases in a non trusted location using a Shift key & check the startup code. If it won't allow me to do that, I don't want to know.

Of course, its worth being sensible but if I had never used code from other sources I would still be a complete novice in Access

Click to expand...

Mark_ said:

Try using the following three functions to determine the initial payment, the monthly payments, and the final payment;

Code:Copy to clipboard

Function InitPay(dtStart As Date, DtEnd As Date, cAmt As Currency)
    Dim aiDays As Integer
    Dim aiMonths As Integer
    Dim acPay As Currency
    
    If DtEnd < dtStart Then InitPay = 0
    If cAmt <= 0 Then InitPay = 0
    
    If Year(DtEnd) > Year(dtStart) Then 'Convert years to months
        aiMonths = (Year(DtEnd) - Year(dtStart)) * 12
    Else
        aiMonths = 0
    End If
    aiMonths = aiMonths + (Month(DtEnd) - Month(dtStart))
    
    If Day(DtEnd) > Day(dtStart) Then
        aiMonths = aiMonths + 1 'If partial month, treat as full.
    End If
    aiDays = 31 - Day(dtStart) 'Treat as 30 days per month...
    If aiDays = 0 Then aiDays = 1
    
    InitPay = ((cAmt / 30) / aiMonths) * aiDays
    
End Function

Function MonthPay(dtStart As Date, DtEnd As Date, cAmt As Currency)
    Dim aiDays As Integer
    Dim aiMonths As Integer
    Dim acPay As Currency
    
    If DtEnd < dtStart Then MonthPay = 0
    If cAmt <= 0 Then MonthPay = 0
    
    If Year(DtEnd) > Year(dtStart) Then 'Convert years to months
        aiMonths = (Year(DtEnd) - Year(dtStart)) * 12
    Else
        aiMonths = 0
    End If
    
    aiMonths = aiMonths + (Month(DtEnd) - Month(dtStart))
    
    If Day(DtEnd) > Day(dtStart) Then
        aiMonths = aiMonths + 1 'If partial month, treat as full.
    End If
    
    aiDays = Day(DtEnd)
    If aiDays > 30 Then aiDays = 30
    
    MonthPay = (cAmt / aiMonths)

End Function

Function FinalPay(dtStart As Date, DtEnd As Date, cAmt As Currency)
    Dim aiDays As Integer
    Dim aiMonths As Integer
    Dim acPay As Currency
    
    If DtEnd < dtStart Then FinalPay = 0
    If cAmt <= 0 Then FinalPay = 0
    
    If Year(DtEnd) > Year(dtStart) Then 'Convert years to months
        aiMonths = (Year(DtEnd) - Year(dtStart)) * 12
    Else
        aiMonths = 0
    End If
    aiMonths = aiMonths + (Month(DtEnd) - Month(dtStart))
    
    If Day(DtEnd) > Day(dtStart) Then
        aiMonths = aiMonths + 1 'If partial month, treat as full.
    End If
    
    aiDays = Day(DtEnd)
    FinalPay = ((cAmt / 30) / aiMonths) * aiDays
    
End Function

I would need to know your business rules for end of month to give a better answer. Many businesses assume that past the 25th of the month it is a full month. If this is true for your business then before the final calculation put in

Code:Copy to clipboard

if aiDays > 25 then aiDays = 30

Otherwise you would need to tell me how February is dealt with.

Each is called with the parameters in the same order; The Date you entered the contract, the Date the contract ends, and the total amount of the contract.

Click to expand...

arnelgp said:

here, this is a free calculation. you have to test it ciz i cant get it show on the click event of dustribution amout of sub form.

Click to expand...

arnelgp said:

if you can provide us in excel the calculation for 3 month, 6 months, 1 year, etc. contract, then we can formulate and come up of a function that will calculate and save it automatically. but of course you have to enter the initial starting date.

Click to expand...