Split Amt bewteen calendar months

[NOL]

Hi ,

Can't figure out this one

The amount "Interest Accrued/Num of days " will give the daily amount of interest between start date and end date .

eg. in the attached excel sheet ,
For facility 1, 101128.87/92 =1099.23 is my amount for each day between 7/1/2002 and 9/30/2002.

What I need finally is , the amount for every calendar month.
ie. the total amount for 7/1/2002 to 8/31/2002 viz. 1099.23*31
and the total amount for 9/1/2002 to 9/30/2002 viz. 1099.23*30

Any ideas or even vague thoughts would be a great help

Thanks,
Gina.

PS: If I am not being clear please let me know and I'll elaborate.

 

[NOL]

YIPPEE!!!

Well I still need to improve on it, but it works !!!
Could anyone help me with tips for improving the performance.
I don't know how it would work for large data...


But here it is ..
****************************************************
Function Interest_Acc(Facility As Variant, Start_Date As String, End_Date As String, Amount As Double) As Variant
' creates the table TAB_INTEREST

Dim Month_Count, Range, As Integer
Dim Prev_Date, Next_Date, Month_End, Facility_ID As String
Dim Interest As Double
Dim dbs As Database

' Calculate number of calendar months into which the record will be split
Range = DateDiff("m", Start_Date, End_Date)
Prev_Date = Start_Date
Facility_ID = Facility
Set dbs = CurrentDb

For Month_Count = 0 To Range
' For the last month use the End_date of the record rather than the last day of the month
If Month_Count <> Range Then
Next_Date = DateSerial(Year(DateValue(Prev_Date)), Month(DateValue(Prev_Date)) + 1, 0)
Else
Next_Date = End_Date
End If
' calculate interest for the number of days in between
Interest = Amount * (DateDiff("d", Prev_Date, Next_Date) + 1)
Month_End = DateSerial(Year(DateValue(Next_Date)), Month(DateValue(Next_Date)) + 1, 0)

'save calculated amt in another table
dbs.Execute " INSERT INTO TAB_INTEREST " _
& "(Facility,EndDate,Amount) VALUES " _
& "('" & Facility_ID & "', '" & Month_End & "','" & Interest & "');"
Prev_Date = DateAdd("d", 1, Next_Date)
Next Month_Count
dbs.Close
Interest_Acc = 0

End Function
****************************************************
I owe a lot to this forum !

Thanks,
Gina .

 

[Cosmos75]

Are you trying to generate a query or a report?

Is the source for the data a table or query?

 

[Cosmos75]

Here's a query that totals the Interest by month, but should I use StartDate or EndDate?

Note: I also included a report, I just took the data from the table, grouped by Start Date (which automatically groups by Month). Did the same using End Date as well.

Edit: Uploaded new version of db file.

 

[NOL]

Hi Cosmos,

Thanks !

The source is a query.
And to get to the output that i need , I have to use two dates a i did in the function , both a start and a end date.
A single date won't do !

It's one interest amount, eg. 1234.56 for 92 days between start date and end date, that needs to be split according to calendar months.

I hope this hasn't got more confusing !!

Gina .

 

[Cosmos75]

I see... this is out of my league , but if I come up with any ideas, I'll be sure to post back!
Sorry!

 

[Cosmos75]

Helpfull?...Hopefully

Calculate Days in Month
http://www.access-programmers.co.uk...p?s=&threadid=33661&highlight=Days+in+a+month

 

[NOL]

U bet it was !!
In fact I've searched the forum at every step of that function i wrote !!!


And know what ?
I just had a meeting with the user and the interest calculation he needs is not the one I have !!

Two days of work down the drain !!! I could cry !!
I wish there were'nt any users !!!

Thanks for all your help though
Gina.

 

[Cosmos75]

NOOOOOOO!!!!!

OH, NO!!!

I am so sorry to hear that!


(I am still going to try to find out how to do what you first asked just for my own curiosity!)
: