Working with dates

[plannerg]

Hello,

I am trying to work out how to find the difference between two dates, in years and days. I have a [date_of_birth] field and another field [date] and wish to work out the difference in years and days as opposed to decimal years. Is it possible to do it?

Thanks.

 

[boblarson]

First of all rename your field that is named "date" as Date is a reserved word and you should not use reserved words as field names as it will only cause you grief later.

I created this in the Access query QBE grid and then am just pasting it here in SQL view for you. You can change the date fields to fit your table and field names and then just paste it into a blank query in SQL view if you wish.

SELECT DateDiff("y",Format([FirstDate],"yyyy"),Format([SecondDate],"yyyy")) AS YearsDiff, DateDiff("d",[FirstDate],[SecondDate])-([YearsDiff]*365) AS DaysDiff, [YearsDiff] & " years " & [DaysDiff] & " days" AS YearsAndDaysDiff
FROM tblDateTest;

There may be a better way, but this seemed to work for me.