Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Other topics FAQ

VBA and Custom Functions

Age Calculation Function by Bullschmidt
Posted: 23 Jun 03

Here is a function to calculate age.  The code could be placed in any module.

An example of use for a control's ControlSource property would be:
=AgeInYears([DOBField], Date())

An example of use for a calculated field in a query would be:
AgeCalcField: AgeInYears([DOBField], Date())

Public Function AgeInYears(ByVal pvarBirthDate As Variant, ByVal pvarCompareDate As Variant) As Long
 ' Purpose:  Calc age in years as of the compare date.
 ' Accepts:  pvarBirthDate      date
 '           pvarCompareDate    date
 ' Returns:  number
 ' Remarks:
 '           Returns 0 if any needed fields blank.
 '           A simple DateDiff("yyyy",[pvarBirthDate],[pvarCompareDate]) is off
 '           by one when the month & day of the birthdate is greater than
 '           the month & day of the compare date.
 '           Access stores the date AND time in each of the date/time fields.
 '           So even though the fields may be formatted to LOOK just like
 '           date only, there also is a time stored.
 '           And the time stored is usually a constant 12:00:00 AM
 '           (effectively 0) which can be seen if you set the field to be
 '           long time format.  Or even in General Date format any time but
 '           12:00:00 AM is shown.  It's usually a good idea to store a current date
 '           using Date() instead of Now() unless one really does want a time
 '           other than 12:00:00 AM to be stored.

 On Error GoTo Err_AgeInYears
 ' Exit if some fields null.
 If IsNull(pvarBirthDate) Or IsNull(pvarCompareDate) Then
  AgeInYears = 0
  Exit Function
 End If
 ' Calc age in years and subtract 1 if the birth month/day is not on or before the
 ' compare month/day.
 ' (I.e. 8/1/1995 birthdate and 9/1/1996 compare date would be age of 1
 ' and 9/1/1995 birthdate and 9/1/1996 compare date would be age of 1
 ' but 9/2/1995 birthdate and 9/1/1996 compare date would be age of 0.)
 If (Month(pvarBirthDate) < Month(pvarCompareDate)) Or (Month(pvarBirthDate) = Month(pvarCompareDate) And Day(pvarBirthDate) <= Day(pvarCompareDate)) Then
  AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate)
  AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) - 1
 End If
 Exit Function

 MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation
 Exit Function
End Function

J. Paul Schmidt, Freelance Access and ASP Developer
http://www.Bullschmidt.com/Login.asp - Database on the Web Demo
http://www.Bullschmidt.com/Access - Sample Access Invoices Database

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close