CODEWITHSUNDEEP
datems-access
Code Novice
Code Novice

Reputation: 2398

Calculate Full Months Between Two Dates in MS ACCESS Query

I'm having an issue using the ACCESS DateDiff function. I'm attempting to obtain the FULL months between two dates however this function ignores dates in the middle of the month. It apparently performs a simple calculation on the month number itself and does not take into account the day of the month.

For example: If I want to know the full month between October 16th and November 3rd. This should return zero however the DateDiff function is returning 1. What code do I need to implement in order to obtain the full months between and have it work for ALL months. I say ALL months as months can have 28 days (29 in a leap year), 30 days and 31 days which is why I wouldn't simply calculate the days between two dates.

Access Function: DateDiff("m",PAID_DATE,STATIC_DATE) AS Months_Between

The screenshot below shows the data I want as well as the incorrect month between results I am obtaining.

enter image description here

Lastly a screenshot of a simple representation of my output in the ACCESS Design View.

enter image description here

Oracle Example of what I'm trying to accomplish in ACCESS

I can accomplish this in Oracle very easy as oracle's Months_Between function takes into account the months and leap years and the days within each month and outputs the actual months between value. If I knew how to get this in ACCESS I wouldn't have posted this question.

enter image description here

Upvotes: 1

Views: 5896

Answers (1)

Gustav
Gustav

Reputation: 55806

You'll need a custom function like this:

Public Function Months( _
  ByVal datDate1 As Date, _
  ByVal datDate2 As Date, _
  Optional ByVal booLinear As Boolean) _
  As Integer

' Returns the difference in full months between datDate1 and datDate2.
'
' Calculates correctly for:
'   negative differences
'   leap years
'   dates of 29. February
'   date/time values with embedded time values
'   negative date/time values (prior to 1899-12-29)
'
' Optionally returns negative counts rounded down to provide a
' linear sequence of month counts.
' For a given datDate1, if datDate2 is decreased stepwise one month from
' returning a positive count to returning a negative count, one or two
' occurrences of count zero will be returned.
' If booLinear is False, the sequence will be:
'   3, 2, 1, 0,  0, -1, -2
' If booLinear is True, the sequence will be:
'   3, 2, 1, 0, -1, -2, -3
'
' If booLinear is False, reversing datDate1 and datDate2 will return
' results of same absolute Value, only the sign will change.
' This behaviour mimics that of Fix().
' If booLinear is True, reversing datDate1 and datDate2 will return
' results where the negative count is offset by -1.
' This behaviour mimics that of Int().

' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28. when adding a count of months to dates of Feb. 29.
' when the resulting year is a common year.
'
' 2010-03-30. Cactus Data ApS, CPH.

  Dim intDiff   As Integer
  Dim intSign   As Integer
  Dim intMonths As Integer

  ' Find difference in calendar months.
  intMonths = DateDiff("m", datDate1, datDate2)
  ' For positive resp. negative intervals, check if the second date
  ' falls before, on, or after the crossing date for a 1 month period
  ' while at the same time correcting for February 29. of leap years.
  If DateDiff("d", datDate1, datDate2) > 0 Then
    intSign = Sgn(DateDiff("d", DateAdd("m", intMonths, datDate1), datDate2))
    intDiff = Abs(intSign < 0)
  Else
    intSign = Sgn(DateDiff("d", DateAdd("m", -intMonths, datDate2), datDate1))
    If intSign <> 0 Then
      ' Offset negative count of months to continuous sequence if requested.
      intDiff = Abs(booLinear)
    End If
    intDiff = intDiff - Abs(intSign < 0)
  End If

  ' Return count of months as count of full 1 month periods.
  Months = intMonths - intDiff

End Function

Upvotes: 2

Related Questions