how to calculate age for access

I have input the date of birth. Please show me how to set formula to calculate age. Thanks.
You might like to take a look at Age.zip in my public databases folder at:

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,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

3 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-2020

2 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

2 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 February 28, 2021 Views 7,783 Applies to: