Calculate Age

[ghanif]

What is the querry to show the age of a person (Year, Month, day) in any given date based on Birth date input.

[smUX]

Try searching...someone asked the same question recently

[ghanif]

Thanks, do you know the query?

[smUX]

No, try searching...it was answered

[cssriraman]

Here is the code:

'A function which accepts date of birth from and returns age in exact years, months and days
Public Function ExactAge(BirthDate As Variant) As String
    Dim yer As Integer, mon As Integer, d As Integer
    Dim dt As Date
    Dim sAns As String
 
    If Not IsDate(BirthDate) Then Exit Function
    dt = CDate(BirthDate)
    If dt > Now Then Exit Function
 
    yer = Year(dt)
    mon = Month(dt)
    d = Day(dt)
    yer = Year(Date) - yer
    mon = Month(Date) - mon
    d = Day(Date) - d
 
    If Sgn(d) = -1 Then
        d = 30 - Abs(d)
        mon = mon - 1
    End If
 
    If Sgn(mon) = -1 Then
        mon = 12 - Abs(mon)
        yer = yer - 1
    End If
 
    sAns = yer & " year(s) " & mon & " month(s) " & d & " day(s) old."
    ExactAge = sAns
End Function

[bushmobile]

there are a few suggestions in this thread: http://www.vbforums.com/showthread.php?t=410159

[ghanif]

sorry I am new with this forum, I shall do it

[RobDog888]

You would use any of the date functions like DateDiff for example.
What Office App are you using?

[ghanif]

I am using Access2000, try it DateDiff does not work.
Say someone is born in May 10, 2000, I want the query to tell me 6 y, 1m, 3 d

[RobDog888]

Yes, it works but you need to build your format if you want it like that. Pass one call for the years and then another for the months from birth month to current date then again for days, etc.

[ghanif]

Could you post an example I am not sure if I understand what you mean by [Pass one call]

[DKenny]

Function GetAge(BirthDate As Date) As String
Dim dNow As Date
Dim lTemp As Long
Dim sTemp As String
    
    dNow = Now()
    
    '---------------------------------------
    ' Year Part
    '---------------------------------------
    lTemp = DateDiff("yyyy", BirthDate, dNow)
    
    If lTemp = 1 Then
        sTemp = CStr(lTemp) & " Year, "
    Else
        sTemp = CStr(lTemp) & " Years, "
    End If
    
    
    BirthDate = DateAdd("yyyy", lTemp, BirthDate)
    
    
    '---------------------------------------
    ' Month Part
    '---------------------------------------
    lTemp = DateDiff("m", BirthDate, dNow)
    
    If lTemp = 1 Then
        sTemp = sTemp & CStr(lTemp) & " Month, "
    Else
        sTemp = sTemp & CStr(lTemp) & " Months, "
    End If
    
    BirthDate = DateAdd("m", lTemp, BirthDate)
    
    
    '---------------------------------------
    ' Day Part
    '---------------------------------------
    lTemp = DateDiff("d", BirthDate, dNow)
    
    If lTemp = 1 Then
        sTemp = sTemp & CStr(lTemp) & " Day"
    Else
        sTemp = sTemp & CStr(lTemp) & " Days"
    End If
    
    '---------------------------------------
    ' Output
    '---------------------------------------
    GetAge = sTemp
    
End Function

[ghanif]

thank you very much I will try it

[RobDog888]

Just something like this...

Dim sAge As String
sAge = DateDiff("yyyy", "5/10/2000", Date) & " years"
MsgBox sAge

Edit: Should have refreshed.

Note: It will return only the years and not partials.

[ghanif]

The date field is available from the list of individuales that are in the database and we do not want to enter their date of birth eveytime we want to see their age.

[DKenny]

OK, so just add this custom function to the DB and then call it in a query, passing the DOB of the current record.