[AccessD] Date Diff Help It cant be this hard

Hi Stuart

Your method will work for all dates of birth prior to or on 28th. February only. For later dates it will always fail for dates between a normal and a leap year or vice versa. A few examples:

dateborn=#3/29/1946#
datenow=#3/28/1996#

dateborn=#2/29/1948#
datenow=#2/28/1997#

dateborn=#3/1/1944#
datenow=#3/1/1997#

The important part to understand is, that age for humans is not counted by the year, the month or the day but by the day of the month, and as the length of February varies between normal years and leap years, you will have to take leap years into account.
Your code may be expanded to do that. 

My code does it this way:

  ' Maximum number of days in a month.
  Const cbytMonthDaysMax  As Byte = 31

<snip>

    ' Calculate day difference using months and days as Days() will fail when
    ' comparing leap years with non leap years for dates after February.
    intDaysDiff = (Month(datDate1) * cbytMonthDaysMax + Day(datDate1)) - (Month(datDate2) * cbytMonthDaysMax + Day(datDate2))
    intReversed = Sgn(intYears)
    ' Decrease count of years by one if dates are closer than one year.
    intYears = intYears + (intReversed * ((intReversed * intDaysDiff) > 0))

It can be done simpler if you don't the function to be symmetrical (count negative age as well).

Further to this, you must take care of the special case that people born on 29th. February have birthday on 28th. February in normal years. That's what this part of the code does:

  ' Constants for leap year calculation. Last normal date of February.
  Const cbytFebMonth      As Byte = 2
  Const cbytFebLastDay    As Byte = 28

<snip>

    ' Check for ultimo February and leap years.
    If (Month(datDate1) = cbytFebMonth) And (Month(datDate2) = cbytFebMonth) Then
      ' Both dates fall in February.
      ' Check if dates are at ultimo February.
      If (Day(datDate1) >= cbytFebLastDay) And (Day(datDate2) >= cbytFebLastDay) Then
        ' Both dates are at ultimo February.
        ' Check if the dates fall in leap years.
        If Day(DateSerial(Year(datDate1), cbytFebMonth + 1, 0)) = cbytFebLastDay Xor _
          Day(DateSerial(Year(datDate2), cbytFebMonth + 1, 0)) = cbytFebLastDay Then
          ' Only one date falls within a leap year.
          ' Adjust both dates to day 28 of February.
          datDate1 = DateAdd("d", cbytFebLastDay - Day(datDate1), datDate1)
          datDate2 = DateAdd("d", cbytFebLastDay - Day(datDate2), datDate2)
        Else
          ' Both dates fall either in leap years or non leap years.
          ' No adjustment needed.
        End If
      End If

Note the use of Xor, my favourite operator (this is for you Susan, if you are with us).

/gustav


>>> stuart at lexacorp.com.pg 09-01-2006 23:22 >>>
On 9 Jan 2006 at 18:24, Gustav Brock wrote:

> Hi Arthur
> 
> I love the word "reliably" ...
> Try this:
> 
> ? Int(DateDiff("d", #1/9/1955#, Date())/365.26)
> ? Int(DateDiff("d", #1/9/1956#, Date())/365.26)
> 
> It would be nice but there is no simple and foolproof way.

Can you come up with an example that breaks the code I posted ealier?

Datediff("yyyy",dateborn,datenow) + _
(DatePart("y", datenow) < DatePart("y",dateborn))

-- 
Stuart