Access date/time formula?

I have an age field and a DOB (in mm/dd/yyyy format) field in Access 2003. What is the formula to calculate age from the current date in years.

2 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    The simple way to determine time intervals between two dates is via the DateDiff function.

    DateDiff("yyyy", DOB, Now()) will give you the number of years between the two dates. However, it isn't always accurate for ages. For example, as Microsft states: "When comparing December 31 to January 1 of the immediately succeeding year, DateDiff for Year ("yyyy") returns 1 even though only a day has elapsed."

    If you can do some coding, that would be the best way. Here's a quick function:

    Private Sub GetAge(DOB as date) as Integer

    Dim DOBMonth as integer, DOBDay as integer

    Dim NowMonth as integer, NowDay as integer

    If Not IsDate(DOB) Then

    GetAge=0

    Exit Sub

    End If

    If DOB>Now() Then

    GetAge=0

    Exit Sub

    End If

    DOBMonth=Month(DOB)

    DOBDay=Day(DOB)

    NowMonth=Month(Now())

    NowDay=Day(Now())

    If DOBMonth<NowMonth Then

    GetAge=Year(Now())-Year(DOB)

    Elseif DOBMonth=NowMonth Then

    If DOBDay>NowDay Then

    GetAge=Year(Now())-Year(DOB)-1

    Else

    GetAge=Year(Now())-Year(DOB)

    End If

    Else

    GetAge=Year(Now())-Year(DOB)-1

    End If

    End Sub

    Note that if the DOB passed is not a date, or is a future date, the function will return 0.

    I did not have time to verify this function, but I think you'll be able to debug any errors. Pretty straightforward. Good luck.

  • Mark G
    Lv 4
    1 decade ago

    DateDiff("yyyy", "6-Nov-2006",Date())

Still have questions? Get your answers by asking now.