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.
- 1 decade agoFavorite 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
If DOB>Now() Then
If DOBMonth<NowMonth Then
Elseif DOBMonth=NowMonth Then
If DOBDay>NowDay Then
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 GLv 41 decade ago