Calculate age (Access XP Professional)

Topic

New Reply

Access stores dates in the form of the number of days since 31 December 1899. So if you subtract two dates, you get the number of days between them.

You can calculate age using a little custom function. Copy the following code to a standard module:

Function Age(Date1, Date2) As Integer
‘ Returns the Age in years between 2 dates
‘ Doesn’t handle negative date ranges i.e. Date1 > Date2
Age = Year(Date2) – Year(Date1)
If Month(Date2) < Month(Date1) Or (Month(Date2) = Month(Date1) And Day(Date2) < Day(Date1)) Then
Age = Age – 1
End If
End Function

Use this function in a query to define a calculated field:

TheAge: Age([Date of birth], [Start of Year])

or as control source of a text box on a form or report:

=Age([Date of birth], [Start of Year])

Reply | Quote

Replies

(Edited by HansV on 02-Sep-03 12:51. Changed subject to something more descriptive than just ‘Access’.)

Hello ,

Is there a way of calculating an age of something in a query i am trying to obtain an age in years of someone, I have tried an expression of
Age =[ Start of Year]-[ Date of Birth] which comes out with a very high figure ie 4569.
My query consists of 3 Columns Name Start of year(ie 01 January 2003) Date of Birth (ie 12 July 1989), i need an expression to automatialy calculate the Age in Years. For every name the Start of year date will be the same or i might want to change it to the Current Date. Any help would be appreciated

Reply | Quote

Try this user defined function:

Public Function fGetAgeAsOf(dtDOB As Variant, dtAsOf As Variant) As Integer
Dim dtBDay As Date
If Not IsDate(dtDOB) Or Not IsDate(dtAsOf) Then Exit Function
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
fGetAgeAsOf = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End Function

HTH
RDH

Reply | Quote

uh oh. I have been using the following to calculate age. It seemed to work ok, but now, based on these kewl funtions presented here, I am concerned it may be incorrect.

‘Age in years is Current date (dtmToday) less Birth date (dtmBdate)
Int((dtmToday-dtmBdate)/365.25)

In a query it looks like:
Age: Int((dtmToday-dtmBdate)/365.25)

Is this oversimplified?

Thanks,

Ken

Reply | Quote

I think this formula may fail for certain specific situations. I believe those situations are when today’s date is on or just before the birthdate (month/day, that is), and may also be dependent on if date is before March 1. The result isn’t catastrophic, a person who will be 21 tomorrow may calculate as 21 today.

Reply | Quote

I think this formula may fail for certain specific situations. I believe those situations are when today’s date is on or just before the birthdate (month/day, that is), and may also be dependent on if date is before March 1. The result isn’t catastrophic, a person who will be 21 tomorrow may calculate as 21 today.

Reply | Quote

uh oh. I have been using the following to calculate age. It seemed to work ok, but now, based on these kewl funtions presented here, I am concerned it may be incorrect.

‘Age in years is Current date (dtmToday) less Birth date (dtmBdate)
Int((dtmToday-dtmBdate)/365.25)

In a query it looks like:
Age: Int((dtmToday-dtmBdate)/365.25)

Is this oversimplified?

Thanks,

Ken

Reply | Quote

Try this user defined function:

Public Function fGetAgeAsOf(dtDOB As Variant, dtAsOf As Variant) As Integer
Dim dtBDay As Date
If Not IsDate(dtDOB) Or Not IsDate(dtAsOf) Then Exit Function
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
fGetAgeAsOf = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End Function

HTH
RDH

Reply | Quote