Calculate Number of Years, Months and Days between 2 dates

[carl.barrett]

Hi,

I have a continuous form based on a query ( I will also be creating a
report based on the same query).

There are 2 fields:

Date Obtained and Date Of Expiry

I want a further 3 columns to the right of these 2 fields to show the
number of Years, Months and Days between the 2 given dates.

In the query I have the dates as the first 2 columns. In the third
column I have:

Column 3 Has - Years: DateDiff("yyyy",[DateObtained],[DateOfExpiry])
Column 4 Has - Months: DateDiff("m",[DateObtained],[DateOfExpiry])
Column 5 Has - Days:
DateDiff("d",DateAdd("m",[Months],[DateObtained]),[DateOfExpiry])

However my data is not coming out quite how I would like it, for
example

Date Obtained Date Of Expiry Years Months Days
14/06/05 14/06/06 1 12 0
30/06/05 03/08/05 1 2 -27

On the first example I just want it to show: 1 0 0
On the second I want it to show: 0 1 3

I hope this makes sense and hope someone can help me out here.

Please make any recommended solutions as clear as possible.

Thanks in advance to anyone who takes the time to help me out here.

Regards.

[David Lloyd]

Carl:

Below is a query that, from my limited testing at least, appears to give you
the information you are looking for. Your second example says the result
should be 0 1 3, however, my calculations return 0 1 4, given that July is
31 days, plus the first three days of August. I had to adjust the month
calculation for the obtained Day being less than or equal the Expired Day as
the DateDiff function just subtracts Months.

SELECT TestDate.DateObtained, TestDate.DateExpired,
DateDiff("yyyy",[DateObtained],[DateExpired]) AS Years,
IIf(Day([DateObtained])<=Day([DateExpired]),DateDiff("m",[DateObtained],[DateExpired])-[Years]*12,DateDiff("m",[DateObtained],[DateExpired])-[Years]*12-1)
AS Months,
DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateExpired])
AS Days
FROM TestDate;

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
Hi,

I have a continuous form based on a query ( I will also be creating a
report based on the same query).

There are 2 fields:

Date Obtained and Date Of Expiry

I want a further 3 columns to the right of these 2 fields to show the
number of Years, Months and Days between the 2 given dates.

In the query I have the dates as the first 2 columns. In the third
column I have:

Column 3 Has - Years: DateDiff("yyyy",[DateObtained],[DateOfExpiry])
Column 4 Has - Months: DateDiff("m",[DateObtained],[DateOfExpiry])
Column 5 Has - Days:
DateDiff("d",DateAdd("m",[Months],[DateObtained]),[DateOfExpiry])

However my data is not coming out quite how I would like it, for
example

Date Obtained Date Of Expiry Years Months Days
14/06/05 14/06/06 1 12 0
30/06/05 03/08/05 1 2 -27

On the first example I just want it to show: 1 0 0
On the second I want it to show: 0 1 3

I hope this makes sense and hope someone can help me out here.

Please make any recommended solutions as clear as possible.

Thanks in advance to anyone who takes the time to help me out here.

Regards.

[carl.barrett]

Hi David,

Thanks very much for your help. This just about does it however I have
noticed that if the dates are:

1/4/05 and 31/3/08 the answer it gives is: 3 -1 30 where I think
it should be 2 11 30

Everything else seems to be spot on. Could you help me get around this
please.

Thanks again for your help.

[Gord]

If the pure query approach gets too messy you can try pasting the
following code into a module and then use:

Years: MyDateDiff("yyyy",[DateObtained],[DateOfExpiry])
Months: MyDateDiff("m",[DateObtained],[DateOfExpiry])
Days: MyDateDiff("d",[DateObtained],[DateOfExpiry])

---8<------8<------8<------8<------8<------8<------8<---

Public Function MyDateDiff(Interval, date1, date2)
Dim years, months, days, d1, d2
If IsNull(date1) Or IsNull(date2) Then
MyDateDiff = Null
Exit Function
End If
' ensure that d1 precedes d2
If date1 < date2 Then
d1 = date1
d2 = date2
Else
d1 = date2
d2 = date1
End If
years = DateDiff("yyyy", d1, d2)
d1 = DateAdd("yyyy", years, d1)
months = DateDiff("m", d1, d2)
If months < 0 Then
years = years - 1
months = months + 12
d1 = DateAdd("yyyy", -1, d1)
End If
d1 = DateAdd("m", months, d1)
days = DateDiff("d", d1, d2)
Select Case Interval
Case "d"
MyDateDiff = days
Case "m"
MyDateDiff = months
Case Else
MyDateDiff = years
End Select
End Function

[David Lloyd]

Carl:

I adjusted the Years calculation to adjust for the month of the origin date
being greater than the expire date. This is the same logic I applied to the
Months calculation. With the adjustment, I return 2 11 30 for your example.

SELECT TestDate.DateObtained, TestDate.DateExpired,
IIf(Month([DateObtained])<=Month([DateExpired]),DateDiff("yyyy",[DateObtained],[DateExpired]),DateDiff("yyyy",[DateObtained],[DateExpired])-1)
AS Years,
IIf(Day([DateObtained])<=Day([DateExpired]),DateDiff("m",[DateObtained],[DateExpired])-[Years]*12,DateDiff("m",[DateObtained],[DateExpired])-[Years]*12-1)
AS Months,
DateDiff("d",DateAdd("m",[Months],DateAdd("yyyy",[Years],[DateObtained])),[DateExpired])
AS Days
FROM TestDate;

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...
Hi David,

Thanks very much for your help. This just about does it however I have
noticed that if the dates are:

1/4/05 and 31/3/08 the answer it gives is: 3 -1 30 where I think
it should be 2 11 30

Everything else seems to be spot on. Could you help me get around this
please.

Thanks again for your help.

[carl.barrett]

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.

[carl.barrett]

Hi,

It's OK about the #Error thing, I asked the question before I put any
thought into it ... Sorry.

Here's what I put, don't know if it's an efficient way of doing it but
it seemed to work.

Cheers and thanks again for your time.

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