Re: Calculate Number of Years, Months and Days between 2 dates

Hi David,

Thanks again for your response. I managed to get the same answer as
you using the following:

SELECT tblIncident.DateObtained, tblIncident.DateOfExpiry,
DateDiff("yyyy",[DateObtained],[DateOfExpiry])-IIf(Format([DateObtained],"mmdd")>Format([DateOfExpiry],"mmdd"),1,0)
AS Years,
IIf(Day([DateObtained])<=Day([DateOfExpiry]),DateDiff("m",[DateObtained],[DateOfExpiry])-[Years]*12,DateDiff("m",[DateObtained],[DateOfExpiry])-[Years]*12-1)
AS Months,
DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateOfExpiry])
AS Days
FROM tblIncident;

Thanks again for taking the time in helping me out.

Could you tell me how to list the output so that it does not show
#Error if I have no DateObtained or DateOfExpiry in the table.

You gave me an example of putting this into a module. Can it be done
in a module using the SQL statements we have come up with as your
module version didn't give an error when fields were empty.

Thanks again for any pointers.

Regards.

.

Relevant Pages