<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1170" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi All</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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. </FONT></DIV>
<DIV><FONT face=Arial size=2>Anyway, here t'is, most of it is
comments.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Function Age_Calc(birthdate As Date, dateto As
Date) As Integer<BR>On Error GoTo eror<BR><FONT color=#008000>'if a dateto
didn't get sent, use today<BR></FONT>Dim birthtime As Variant<BR>Dim nowtime As
Variant<BR>Dim nowyear As Variant<BR><FONT color=#008000>'get a number for the
date of the year, not using yeardate format because of leap
years<BR></FONT>birthtime = DatePart("m", birthdate) & Format(DatePart("d",
birthdate), "00")<BR><FONT color=#008000>'we need to work out what day it is we
want to enquire to<BR></FONT>nowtime = CInt(DatePart("m", dateto) &
Format(DatePart("d", dateto), "00"))<BR><FONT color=#008000>'let's just get the
year difference<BR></FONT>nowyear = DatePart("yyyy", dateto) - DatePart("yyyy",
birthdate) <FONT color=#008000>'eg 2003-1965<BR>'takeaway where we are
calculating to using the dateto<BR></FONT>birthtime = nowtime - birthtime + 1
<FONT color=#008000>'should we count down for the age?<BR></FONT>If birthtime
> 0 Then <FONT color=#008000>'they were born before the calc to
date</FONT></FONT></DIV>
<DIV><FONT face=Arial size=2> Age_Calc =
nowyear<BR>Else<BR> Age_Calc = nowyear - 1<BR>End If<BR>Exit
Function<BR>eror:<BR> MsgBox Err.Number & " : " &
Err.Description<BR>End Function<BR></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>To use it in a query, call the function from the
query:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SELECT age_calc([tablename].[birthdate],Date()) AS
age FROM tablename</DIV></FONT>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>HTH</FONT></DIV>
<DIV><FONT face=Arial size=2>Cheers</FONT></DIV>
<DIV><FONT face=Arial size=2>Selina</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV>
<HR align=center width=400 color=#df18b7 noShade>
<TABLE cellSpacing=0 cellPadding=0 width=400 align=center border=0>
<TBODY>
<TR>
<TD vAlign=top width=10> </TD>
<TD vAlign=top width=188><FONT
face="Verdana, Arial, Helvetica, sans-serif" size=1><STRONG><FONT
color=#1a51c0>Selina Iddon </FONT><BR></STRONG><A
href="mailto:selina@easydatabases.com.au"><FONT
color=#000000>selina@easydatabases.com.au</FONT></A><BR></FONT></TD>
<TD vAlign=top width=152><FONT
face="Verdana, Arial, Helvetica, sans-serif" size=1>Ph: 0414 225
265<STRONG><BR></STRONG>Easy Access Databases </FONT></TD>
<TD vAlign=top width=50>
<DIV align=center><IMG height=33
src="http://www.digitalfunk.net/Clients/EAD/email_use/small_logo.jpg"
width=33></DIV></TD></TR></TBODY></TABLE></DIV></BODY></HTML>