[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