# 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.

Relevance

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