Adding months to years in queries

[TonyB]

Hi,



I will try and explain as best I can!

I'm writing a query, where I have calculated the number of months between a start date and and end date:

No of Months: Round(([EndDate]-[Startdate])/30.42)

This works fine, but I want to Access to return the result as "X years, and X months". So for example, if the start date is 01/01/2011 and end date is 31/12/2011, I want Access to return a result of "3 years and 0 months", rather than just "36 months". Similarly, if the start date is 01/08/2009 and the end date is 31/01/2010 at the moment it tells me 6 months, and I want it to read as 0 years and 6 months.

I've used:

No of Years: Round(([EndDate]-[Startdate])/365.25)

which is fine, but obviously this will round months to the nearest year (so 6 months becomes 1 year).

I tried using the DateDiff function, but found it to be pretty useless for this scenario, as it doesn't round anything (so 01/01/2011 - 31/12/2013 is calculated as 2 years, rather than 3!)

Grateful for any suggestions

Thanks

[alansidman]

This might get you in the right direction:

http://support.microsoft.com/kb/290190

Alan

[jzwp11]

You have to use the month interval in the datediff() function not the year interval

Something like this perhaps

SELECT INT(DateDiff("m",startdate,enddate)/12) & " years " & DateDiff("m",startdate,enddate)-(int(DateDiff("m",startdate,enddate)/12)*12) & " months " AS ElapsedTime
FROM myTable;