Date differences in MS Access

You can use DATEDIFF  to calculate the difference between 2 dates.
Please check this thread. A similar thread.
https://www.experts-exchange.com/questions/27601269/Access-2007-How-to-calculate-YEARS-of-Service-in-company.html

Thanks for your help but I'm trying to find a solution via a query not by coding if possible.

keep in mind that the difference between 12/31/17 and 1/1/18 is one month:

?datediff("m", #12/31/17#, #1/1/18#)

so as long as you are good with that then Scott's answer would be a good start.

Another method would be to create a function you could call from you query and do the processing there.

You could pass in the start and end date, then keep adding a month to the start date and count the number of increments until the

DateAdd("m", intLoop, StartDate) > EndDate

Then compute years and months as Scott describes above.

You could also try:

DateDiff("m", StartDate, EndDate) + (Day(EndDate) < Day(StartDate))

which would subtract 1 from the number of months if the day of the month of the EndDate is less than the day of the month of the start date.

I'm trying to find a solution via a query not by coding if possible.

You can't. You will have to use a function like this to get it right. And, after all, it's nothing more than a copy-n-paste into a module:

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

Select allOpen in new window

That will returns the exact full count of months. To obtain years and months, do:

TotalMonths = 37  ' example
Years = TotalMonths \ 12
Months = TotalMonths Mod 12

Select allOpen in new window

Thus, your query could look like:

Select *, CStr(Months([Start Date], [End Date]) \ 12) & " years, " & CStr(Months([Start Date], [End Date]) Mod 12) & " months" As YearMonth
From YourTable

Select allOpen in new window

This problem is also related to the age calculation problem.  If you are born on a date (DOB), what is your age, in years, on a later date?  I normally code a VBA function for this.

@LillyC
What do you need the year_count/month_count answer to be for the following:
1. 12/31/17, 1/1/18  - Dale's example
2. 12/15/17, 1/14/18  
3. 12/15/17, 1/15/18  
4. 12/15/17, 1/16/18  
5. 12/15/17, 2/14/19
6. 12/15/17, 2/15/19
7. 12/15/17, 2/16/19

upload a sample database.
Include a table with a few records.
Include a table showing manually the required output.

The results for those dates will be:

1: 0 years, 0 months
2: 0 years, 0 months
3: 0 years, 1 months
4: 0 years, 1 months
5: 0 years, 1 months
5: 0 years, 2 months
6: 0 years, 2 months

Select allOpen in new window

Gustav

I think you should double-check those results :-)

Ah, missed the 19:

1: 0 years, 0 months
2: 0 years, 0 months
3: 0 years, 1 months
4: 0 years, 1 months
5: 1 years, 1 months
5: 1 years, 2 months
6: 1 years, 2 months

Select allOpen in new window

Thanks!

Thanks Scott your solution works well for me. Thanks everyone else for you input.