×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Microsoft: Access Other topics FAQ

## VBA and Custom Functions

 Age Calculation Function by Bullschmidt faq181-3769 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) Else  AgeInYears = DateDiff("yyyy", pvarBirthDate, pvarCompareDate) - 1 End If     Exit FunctionErr_AgeInYears: MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation Exit FunctionEnd FunctionJ. Paul Schmidt, Freelance Access and ASP Developerhttp://www.Bullschmidt.com/Login.asp - Database on the Web Demohttp://www.Bullschmidt.com/Access - Sample Access Invoices Database Back to Microsoft: Access Other topics FAQ Index Back to Microsoft: Access Other topics Forum

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!