[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/20030808/b5d3a562/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: small_logo.jpg
Type: image/jpeg
Size: 1219 bytes
Desc: not available
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030808/b5d3a562/attachment-0001.jpg>