Function to calculate yearly billing amount

[Guest]

I am trying to calculate a year charge amount for each year between two
dates. I have the following data:

ID 1
Tot_BillingAmt $198,810.25
StartDate 01/01/2001
StopDate 12/31/2001
MonthsBetween* 12
MonthAmt* $16,567.52

ID 2
Tot_BillingAmt $18,500.00
StartDate 07/01/2002
StopDate 10/31/2003
MonthsBetween* 16
MonthAmt* $13.718.75

ID 3
Tot_BillingAmt $12,431.35
StartDate 06/01/2003
StopDate 03/31/2005
MonthsBetween* 22
MonthAmt* $565.06

*MonthsBetween = DateDiff(“mâ€,StartDate,StopDate)
*MonthAmt = Tot_BillingAmt / MonthsBetween

*Yearly Billing Amt = MonthAmt * The number of months for that year

Example:
ID 3 has three years that occur between StartDate and StopDate
2003 has 7 months * MonthAmt($565.06) = $3,955.43,
2004 has 12 months * MonthAmt($565.06) = $6,780.74,
2005 has 3 months * MonthAmt($565.06) = $1,695.18

I can not figure out how to calculate The number of months for each year
represented within the record. Does anybody have any suggestions or can
point me in the right direction. I figure I probably have to create a VB
function.

Any help is gretly appreciated.

 

[Guest]

You can use this to find the number of months.
Month(StopDate) - Month(StartDate)

In your first record, it will return 11, so if you want it to return 12, then

Month(StopDate) - Month(StartDate) + 1

 

[Guest]

I am able to get the number of months between using the formula:
*MonthsBetween = DateDiff(“mâ€,StartDate,StopDate)

What I am stuck getting is the number of months within each year within the
date range specified by the startdate and stopdate. See the example, I wnat
to be able to get the numbers 7, 12, and 3.

Example:
ID 3 has three years that occur between StartDate and StopDate
2003 has 7 months * MonthAmt($565.06) = $3,955.43,
2004 has 12 months * MonthAmt($565.06) = $6,780.74,
2005 has 3 months * MonthAmt($565.06) = $1,695.18

 

[Guest]

That would take a little additional coding. Basically, what you need to do
is check the start and end dates and see if the years are the same. If they
are not, then use start date through 12/31/firstyear then 1/1/nextyear
through end date. If it can span more than 2 year, it gets a little more
complicated. How are your VBA skills?

 

[Guest]

I am actually pretty good with VBA I just did not want to reinvent the wheel
if this had already been done. So far I have got this.

1) numyears = DateDiff ("yyyy", StartDate, EndDate)+1

2) If numyears is = 1 then there is only one year.
Only output record:Billyear = Year(StartDate) and nummonths= MonthsBetween.

3) If numyear is > 1 then there are multimple years.
1st record: Billyear = Year(StartDate) and nummonths= Month(StartDate)
2nd record Billyear = Year(StartDate) and nummonths= Month(StopDatetDate)
n=(numyear-2)
Loop through the following n times
next record: Billyear = Year(StartDate)+n and nummonths = 12
end loop

I can use my Id number to link all of the data back up to the original record.

 

[Guest]

Looks like you have it under control. That's would be how I would do it.
(And If you do it like I would, you are obviously an astute and expert
Access developer