April 9, 2024
Contribute to the Microsoft 365 and Office forum!
Click here to learn more 💡
May 10, 2024
Access Forum Top Contributors:
how to calculate age for access
Report abuse
Thank you.
Reported content has been submitted
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.
If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.
This little demo file illustrates the use of a number of age functions. The basic GetAge function does what you require. Copy and paste the basAgeStuff module into your database. You'll then be able to call the function anywhere in the database, passing the date of birth into as its argument.
Ken Sheridan,
Newport, Shropshire, England
"Don't write it down until you understand it!" - Richard Feynman
Report abuse
Thank you.
Reported content has been submitted
5 people found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
There are a variety of ways to achieve this, here are 2
Age: DateDiff("yyyy",[Date of Birth], Date()) + (Date() < DateSerial(Year(Date()), Month([Date of Birth]), Day([Date of Birth])))
Another approach is to simply use a reusable VBA function like:
http://allenbrowne.com/func-08.html
Or
http://www.devhut.net/2010/06/22/ms-access-calculate-the-age/
and call it as required.
Daniel Pineault
Microsoft MVP 2010-2021
Report abuse
Thank you.
Reported content has been submitted
5 people found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
To get it right for any combination of dates, DateAdd must be used, like here:
' Returns the difference in full years from DateOfBirth to current date,
' optionally to another date.
' Returns zero if AnotherDate is earlier than DateOfBirth.
'
' Calculates correctly for:
' leap years
' dates of 29. February
' date/time values with embedded time values
' any date/time value of data type Date
'
' DateAdd() is used for check for month end of February as it correctly
' returns Feb. 28th when adding a count of years to dates of Feb. 29th
' when the resulting year is a common year.
'
' 2015-11-24. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Age( _
ByVal DateOfBirth As Date, _
Optional ByVal AnotherDate As Variant) _
As Integer
Dim ThisDate As Date
Dim Years As Integer
If IsDate(AnotherDate) Then
ThisDate = CDate(AnotherDate)
Else
ThisDate = Date
End If
' Find difference in calendar years.
Years = DateDiff("yyyy", DateOfBirth, ThisDate)
If Years > 0 Then
' Decrease by 1 if current date is earlier than birthday of current year
' using DateDiff to ignore a time portion of DateOfBirth.
If DateDiff("d", ThisDate, DateAdd("yyyy", Years, DateOfBirth)) > 0 Then
Years = Years - 1
End If
ElseIf Years < 0 Then
Years = 0
End If
Age = Years
End Function
Report abuse
Thank you.
Reported content has been submitted
4 people found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Question Info
Last updated April 24, 2024 Views 20,670 Applies to: