<!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.2600.0" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV>
<DIV> Calculation of Age happens to be a universally
gripping topic for programmers. Threads related to this issue keep emerging from
time to time. </DIV>
<DIV> 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.</DIV>
<DIV> 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.</DIV>
<DIV> </DIV>
<DIV> It is also suggested that all relevant postings be done
under a common thread "Re: [AccessD] Age calculation function" so as to
avoid fragmentation.</DIV>
<DIV> </DIV>
<DIV>Regards,</DIV>
<DIV><STRONG>A.D.Tejpal</STRONG></DIV>
<DIV><STRONG>--------------</STRONG></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=selina@easydatabases.com.au
href="mailto:selina@easydatabases.com.au">Selina Iddon</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">Access Developers discussion and
problem solving</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Friday, August 08, 2003 17:32</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [AccessD] Calculate
Age</DIV>
<DIV><BR></DIV>
<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> </DIV></TD></TR></TBODY></TABLE></DIV></BLOCKQUOTE></DIV></BODY></HTML>