Counting the number of months in a date range

[kermit5]

I have a report that requires the user to input a date range. For example, 1/1/03 thru 5/15/03. I need to determine the MonthlyAverage over the specified range. In this case, I would count the number of items and divide by 5 for a montly average.

This is easy to do manually (because I know that the number of months from January to May is 5). How do I get Access to count this for a specified range? Keep in mind that the date range may span more than 1 year. That is to say that the range may be from 1/1/02 thru 3/20/03. I need my program to determine that the number of months is 15.

Is there a way to use datediff or some other function to determine the number of months?

Thanks in advance.

Scott

 

[dcx693]

You can use:
DateDiff("m",#1/1/2002#,#3/20/2003#)
but realize that it returns 14, not the 15 you expected.

If your dates might be entered into the function where the later date is the 2nd parameter, you can consider using the Abs() function to get a positive number.

 

[Rich]

Function GetMonths(dteA, dteB) As Integer

If Day(dteA) <= Day(dteB) Then
GetMonths = DateDiff("m", dteA, dteB) + 1
Else
GetMonths = DateDiff("m", dteA, dteB)
End If