By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Formulas for calculating current age

P: 1
I am an inexperienced access user needing to know how to create a formula for making an age automatically change year by year without manually having to do it. I do not know the language, source codes input used or error codes, but I would just like a simple formula to create a concise database for our church membership.
Aug 27 '08 #1
Share this Question
Share on Google+
12 Replies


Expert Mod 2.5K+
P: 2,545
Hi. I use a custom Visual Basic for Applications function called AgeAtEnrolment to compute the age of students at the date of their enrolment in the college in which I work. It is used like this, in VBA and in SQL:
Expand|Select|Wrap|Line Numbers
  1. CurrentAge = AgeAtEnrolment(EnrolmentDate, DOB)
Expand|Select|Wrap|Line Numbers
  1. SELECT AgeAtEnrolment([EnrolmentDate], [DOB]) AS [Current Age] FROM ...
The first parameter is the date you are calculating the age of the person with reference to (an enrolment date in our case), and the second is the person's date of birth (DOB).

The function returns the actual age of a person as at today's date if you supply the value of today's date as its first parameter:

Expand|Select|Wrap|Line Numbers
  1. SELECT AgeAtEnrolment(Date(), [DOB]) AS [Actual Age] FROM ...
Copy the code below into a public code module - one which is available from the Access Modules tab - or if you do not have such a module already create a new one then paste in the code and save the module under any suitable title (e.g. General Routines).
Expand|Select|Wrap|Line Numbers
  1. Public Function AgeAtEnrolment(CheckDate, TheDOB) As Integer
  2.     Dim RefDate As Date, ThisBirthday As Date, DOB As Date
  3.     Dim YearsDiff As Integer
  4.     Dim BirthdayLater As Boolean
  5.     Dim TheDay As Integer, TheMonth As Integer, TheYear As Integer
  6.     Dim DaysfromRef As Long, DaysfromBirthday As Long
  7.     If IsNull(TheDOB) Or IsNull(CheckDate) Then
  8.         AgeAtEnrolment = 0
  9.     Else
  10.         RefDate = CheckDate
  11.         DOB = TheDOB
  12.         TheDay = day(DOB)
  13.         TheMonth = Month(DOB)
  14.         If Year(DOB) > Year(RefDate) Then
  15.             DOB = DateSerial(Year(DOB) - 100, TheMonth, TheDay)
  16.         End If
  17.         TheYear = Year(RefDate)
  18.         ThisBirthday = DateSerial(TheYear, TheMonth, TheDay)
  19.         DaysfromRef = DateDiff("y", DOB, RefDate)
  20.         DaysfromBirthday = DateDiff("y", DOB, ThisBirthday)
  21.         BirthdayLater = (DaysfromRef < DaysfromBirthday)
  22.         YearsDiff = DateDiff("yyyy", DOB, ThisBirthday)
  23.         If BirthdayLater Then
  24.             YearsDiff = YearsDiff - 1
  25.         End If
  26.         AgeAtEnrolment = YearsDiff
  27.     End If
  28. End Function
-Stewart
Aug 27 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
Well, even with a simple formula, you're going to have to use some code to execute it.This is, perhaps, a little simpler code for for a newbie than Stewart's excellent function. The simple formula is

Expand|Select|Wrap|Line Numbers
  1. Age = DateDiff("yyyy", [DOBField], Date) - IIf(Format$(Date, "mmdd") < Format$([DOBField], "mmdd"), 1, 0) 
  2.  
This will need to be in the AfterUpdate event for the field holding the birthday and in the Form_Current event, so that it will be updated correctly each time you access the record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub DOBField_AfterUpdate()
  2. Me.Age = DateDiff("yyyy", [DOBField], Date) - IIf(Format$(Date, "mmdd") < Format$([DOBField], "mmdd"), 1, 0)
  3. End Sub
  4.  
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. If Not IsNull(Me.DOBField) Then
  3. Me.Age = DateDiff("yyyy", [DOBField], Date) - IIf(Format$(Date, "mmdd") < Format$([DOBField], "mmdd"), 1, 0)
  4. End If
  5. End Sub
Welcome to Bytes!

Linq ;0)>
Aug 27 '08 #3

ADezii
Expert 5K+
P: 8,636
This Function, compliments of the amazing Allen Browne, with a few modifications by myself, will precisely calculate Age as a Whole Number. It will calculate this Age based on a Birth Date and an Optional As Of Date. The code factors in discrepancies that exist with the DateDiff() Function and uses my DOB as an example, with various Inputs and subsequent Outputs. Don't let the amount of code intimidate you, a lot of it is Comments. Any questions, please feel free to ask:
Expand|Select|Wrap|Line Numbers
  1. Function fCalculateAge(varDOB As Variant, Optional varAsOfThisDate As Variant) As Variant
  2. 'Purpose:   Return the Age in Years.
  3. 'Arguments: varDOB = Date Of Birth
  4. 'varAsOfThisDate = The Date to calculate the Age at, or today if missing.
  5.  
  6. The DateDiff() Function simply subtracts the Year parts of the Dates, without
  7. reference to the Month or Day. This means we need to subtract one if the person has not has their birthday this year. This can be handled by a Boolean Expression that returns either -1 or 0 depending on whether or not an individual's
  8. birthday occurred within the Year. See Reference #1.
  9.  
  10. 'Return:    Whole number of years.
  11. Dim dteDOB As Date
  12. Dim dteAsOf As Date
  13. Dim dteBDay As Date     'Birthday in the year of calculation.
  14.  
  15. fCalculateAge = Null    'Initialize to Null
  16.  
  17. 'Validate Parameters
  18. If IsDate(varDOB) Then
  19.   dteDOB = varDOB
  20.  
  21.   If Not IsDate(varAsOfThisDate) Then  'Date to calculate age from.
  22.     dteAsOf = Date      'Then use the Current Date
  23.   Else
  24.     dteAsOf = varAsOfThisDate
  25.   End If
  26.  
  27.   If dteAsOf >= dteDOB Then      'Calculate only if it's after person was born.
  28.     dteBDay = DateSerial(Year(dteAsOf), Month(dteDOB), Day(dteDOB))
  29.                                                         'Reference #1
  30.     fCalculateAge = DateDiff("yyyy", dteDOB, dteAsOf) + (dteBDay > dteAsOf)
  31.   End If
  32. End If
  33. End Function
OUTPUTS:
Expand|Select|Wrap|Line Numbers
  1. 'ADezii's current Age:
  2. Debug.Print fCalculateAge("3/17/1949")
  3.  59 
  4. Debug.Print fCalculateAge(#3/17/1949#)
  5.  59
Expand|Select|Wrap|Line Numbers
  1. 'ADezii's Age upon graduation from MIT:
  2. Debug.Print fCalculateAge("3/17/1949", #6/6/1971#)
  3.  22 
  4. Debug.Print fCalculateAge(#3/17/1949#, "6/6/1971")
  5.  22 
Expand|Select|Wrap|Line Numbers
  1. 'ADezii's Age upon receiving Doctorate Degree in Theoretical Physics
  2. Debug.Print fCalculateAge(#3/17/1949#, #8/15/79#)
  3.  30 
Expand|Select|Wrap|Line Numbers
  1. 'ADezii's Age he hopes to someday attain, hopefully still here responding to Posts (LOL).
  2. Debug.Print fCalculateAge(#3/17/1949#, #3/17/2049#)
  3.  100
Aug 27 '08 #4

NeoPa
Expert Mod 15k+
P: 31,489
...
Expand|Select|Wrap|Line Numbers
  1. 'ADezii's current Age:
  2. Debug.Print fCalculateAge("3/17/1949")
  3.  59 
  4. Debug.Print fCalculateAge(#3/17/1949#)
  5.  59
...
Haven't you forgetten a few zeroes there ADezii :D ?
Aug 30 '08 #5

ADezii
Expert 5K+
P: 8,636
Haven't you forgetten a few zeroes there ADezii :D ?
Everything is true except for the MIT and Theoretical Physics part! (LOL)!
Aug 30 '08 #6

missinglinq
Expert 2.5K+
P: 3,532
Glad to know there's someone here older than I am, if only by six months! ;0)>
Aug 30 '08 #7

ADezii
Expert 5K+
P: 8,636
Glad to know there's someone here older than I am, if only by six months! ;0)>
In some cultures, the elders are revered, but that doesn't seem to be the case here! (LOL).
Aug 30 '08 #8

NeoPa
Expert Mod 15k+
P: 31,489
Anyone know what these two old codgers are going on about??

No worries guys. You're making me feel young again - if only by comparison. It's been TOOOO long since I was a cheeky young whippersnapper :D

PS. You guys give us good reason for respecting our elders. Keep it up :)
Aug 30 '08 #9

missinglinq
Expert 2.5K+
P: 3,532
I've got shoes that are older than you are, NeoPa! And they're not even broken in yet!

;0)>
Aug 30 '08 #10

NeoPa
Expert Mod 15k+
P: 31,489
I wouldn't try to now. They'll be a bit small for a grown man :D
Aug 30 '08 #11

P: 69
I can give you lot at least 10 years, so I'm definitely looking for respect. :-)
Aug 31 '08 #12

NeoPa
Expert Mod 15k+
P: 31,489
It strikes me that it actually feels quite cool to be the youngest :)
Sep 1 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.