[AccessD] Age calculation function

Ding Ding Ding!! That is the correct answer!

Thanks, Charles

Mark Whittinghill
Symphony Information Services
612-333-1311
mwhittinghill at symphonyinfo.com
----- Original Message ----- 
From: "Wortz, Charles" <CWortz at tea.state.tx.us>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Thursday, July 31, 2003 3:05 PM
Subject: RE: [AccessD] Age calculation function


> Mark,
>
> DateDiff() doesn't round up.  The problem is it counts the number of
> time intervals crossed between the two dates.  If you use "yyyy" as the
> time interval, then it counts 30 time intervals (1974, 1975, ..., 2003)
> between #1973/08/01# and #2003/07/31#.  If you use months and divide by
> 12 you should get the correct answer.  The trick to DateDiff is to use
> the next smaller time interval and then adjust the answer to the time
> interval you want.
>
> Charles Wortz
> Software Development Division
> Texas Education Agency
> 1701 N. Congress Ave
> Austin, TX 78701-1494
> 512-463-9493
> CWortz at tea.state.tx.us
>
>
>
> -----Original Message-----
> From: Mark Whittinghill [mailto:mwhittinghill at symphonyinfo.com]
> Sent: Thursday 2003 Jul 31 14:52
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Age calculation function
>
> Hi all,
>
>   I need an age calculation function to calculate current age from
> birthday. If the person's 30th birthday is on August 1, I want them to
> be 29 on July31st.  DateDiff rounds up and makes them 30.  I made this
> function
>
> Public Function CalculateAge(datBirth As Date) As Long
>
>     Dim lngNumDays As Long
>     lngNumDays = DateDiff("d", datBirth, Date)
>
>     CalculateAge = Int(lngNumDays / 365.25)
>
> End Function
>
>
> It works, except when today's date is their birthday.  In my example, on
> their birthday, the function returns 29.  The next day it works.  It
> works for some years on the birthday and not others.  How can I make
> this foolproof?
>
> Mark Whittinghill
> Symphony Information Services
> 612-333-1311
> mwhittinghill at symphonyinfo.com
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>