DOB - Age in Yrs. Months.

[chris.mo]

Please can someone tell me an expression to show an age field in a query from the DOB (short date) field in a table. Just in Yrs would do but yrs/months would be nice.

Thanks.

Chris.

 

[R. Hicks]

Here is a function that will give you the age in Years, Months, and Days. If you only want the Years and Months, reply back and I will alter the function for you if you can not do this yourself.

Function GetAgeYMD(DOB As Variant) As String
Dim intYears As Integer, intMonths As Integer, intDays As Integer
Dim strTmpString As String

If Not IsDate(DOB) Then Exit Function

intMonths = Int(DateDiff("m", DOB, Date))

intYears = Int(intMonths / 12)

intDays = DateDiff("d", DateAdd("m", intMonths, DOB), Date)

intMonths = Int(intMonths Mod 12)

If intDays < 0 Then
&nbsp intMonths = intMonths - 1
&nbsp intDays = DateDiff("d", DateAdd("m", -1, Date) - intDays, Date)
End If

If intMonths < 0 Then
&nbsp intYears = intYears + intMonths
&nbsp intMonths = 12 + intMonths
End If

If intYears = 1 Then
&nbsp strTmpString = intYears & " Year "
Else
&nbsp strTmpString = intYears & " Years "
End If
If intMonths = 1 Then
&nbsp strTmpString = strTmpString & intMonths & " Month "
Else
&nbsp strTmpString = strTmpString & intMonths & " Months "
End If
If intDays = 1 Then
&nbsp strTmpString = strTmpString & intDays & " Day "
Else
&nbsp strTmpString = strTmpString & intDays & " Days "
End If

GetAgeYMD = strTmpString

End Function

HTH
RDH

 

[chris.mo]

Wow! Thanks a lot Mr. Hicks, your response is much appreciated.

However, I think we must have our wires crossed. I had had some success with this...

Expr1: (Date()-[Goalkeepers]![Date of birth])/365.33

...Simply subtracting the DOB date field from the current date then dividing by 365.33. This gives the right age, but with a load of decimal places I cant get rid of even by setting the decimal places to 0 in the format property box??

I might be just ignorant here but isnt there a shorter way than you suggest? I dont wish to appear ungrateful but im a bit cconfused.

Chris.