Calculating age from Date of birth in a Access Query Field

[Australia]

Hi,

Can somebody help me how to calculate age from "Date Of Birth" field in a Access query?

Thanks!

 

[UKRacer]

You can use the following formula in the query - assuming the field containing the date of birth in your table is called "dateofbirth" (!)

Current Age: Int((Now()-[dateofbirth])/365.25)

There may be other ways using DatePart, but this works, so hey!

Pete

 

[MichaelRed]

NOt really. The simplistic thought will be wrong (off by +/- 1) approx. 1/2 the time.

for a 'simplistic (but all to common) example:

DOB = DateAdd("d", 1, Now)
? DOB
5/27/02 1:14:16 PM

? Int((Now()-DOB)/365.25)
-1

DOB = DateAdd("d", -1, Now)
? DOB
5/25/02 1:15:39 PM

? Int((Now()-DOB)/365.25)
0

A modest improvement (albeit at the cost of actual CODE):

Public Function basDOB2Age(Dob As Date, Optional AsOf As Date = -1) As Integer

    'Dob is just the date of Birth
    'AsOf is an optional date to check - as in examples 1 & 2
    'Otherwise, the DOB is checked against the Current Date

    'Michael Red 12/15/2001
    'To Calculate Age from Date of Birth
    
    'Sample Useage:
    '? basDOB2Age(#8/21/1942#, #8/21/2022#)
    '80

    '? basDOB2Age(#8/21/1942#, #8/20/2022#)
    '79

    '? basDOB2Age(#8/21/1942#)
    '59


    Dim tmpAge As Integer       'Simple DateDiff w/o Birthday correction
    Dim BrthDayCorr As Boolean  'BirthDay Before or After date in question

    If (AsOf = -1) Then         'Check for (Optional Date to Check against)
        AsOf = Date             'If Not Supplied, Assume Today
    End If

    tmpAge = DateDiff("YYYY", Dob, AsOf)        'Just the Years considering Jan 1, Mam
    BrthDayCorr = DateSerial(Year(AsOf), Month(Dob), Day(Dob)) > AsOf   'Check This Year

    basDOB2Age = tmpAge + BrthDayCorr           'Just Years and Correction
    
End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over