[AccessD] Age calculation function

    Calculation of Age happens to be a universally gripping topic for programmers. Threads related to this issue keep emerging from time to time. 
    Devising a function capable of giving truly consistent results in terms of years, months and days lapsed between two dates remains a specially sought after objective. Many amongst programming community feel tempted to prepare custom functions towards this end. However, a consensus as to which particular function can be said to have met all tests for consistency, is probably yet to be established.
    Interested members of this forum might like to give their suggestions regarding tests that the ideal function should be required to satisfy. An overall list incorporating these suggestions could help identify the most suitable function / functions.

    It is also suggested that all relevant postings be done under a common thread  "Re: [AccessD] Age calculation function" so as to avoid fragmentation.

Regards,
A.D.Tejpal
--------------
  ----- Original Message ----- 
  From: Selina Iddon 
  To: Access Developers discussion and problem solving 
  Sent: Friday, August 08, 2003 17:32
  Subject: Re: [AccessD] Calculate Age


  Hi All

  sorry to throw back to an old thread, but I couldn't remember if anyone actually came up with the final answer.  If you're interested, I use the following code and (to date) haven't found it ever error.  If you wanted to do it to the hour you'd just add the next datepart I suppose.  
  Anyway, here t'is, most of it is comments.

  Function Age_Calc(birthdate As Date, dateto As Date) As Integer
  On Error GoTo eror
  'if a dateto didn't get sent, use today
  Dim birthtime As Variant
  Dim nowtime As Variant
  Dim nowyear As Variant
  'get a number for the date of the year, not using yeardate format because of leap years
  birthtime = DatePart("m", birthdate) & Format(DatePart("d", birthdate), "00")
  'we need to work out what day it is we want to enquire to
  nowtime = CInt(DatePart("m", dateto) & Format(DatePart("d", dateto), "00"))
  'let's just get the year difference
  nowyear = DatePart("yyyy", dateto) - DatePart("yyyy", birthdate) 'eg 2003-1965
  'takeaway where we are calculating to using the dateto
  birthtime = nowtime - birthtime + 1 'should we count down for the age?
  If birthtime > 0 Then 'they were born before the calc to date
      Age_Calc = nowyear
  Else
      Age_Calc = nowyear - 1
  End If
  Exit Function
  eror:
      MsgBox Err.Number & " : " & Err.Description
  End Function


  To use it in a query, call the function from the query:

  SELECT age_calc([tablename].[birthdate],Date()) AS age FROM tablename

  HTH
  Cheers
  Selina


------------------------------------------------------------------------------
          Selina Iddon 
        selina at easydatabases.com.au
       Ph: 0414 225 265
        Easy Access Databases   

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030811/cfdfcb57/attachment-0001.html>