Hi Everyone

Can someone tell me how I can set up a field to calculate the age of
someone in months and years.

I have a field with the date of birth (short date)
I would like to set up a second calculated field with age in months
and years against the current date or a fixed date.

Hope someone can help me - it's a niggling little problem.

Thanks in advance.

John

fishezuk (AT) btopenworld (DOT) com (FishezUk) wrote in message news:<3b07e12c.0308010652.4c64ce01 (AT) posting (DOT) google.com>...
Try this.....................

Function Age (varBirthDate As Variant) As Integer
Dim varAge As Variant

If IsNull(varBirthdate) then Age = 0: Exit Function

varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function

Public Function basAgeCalcExt(bdt As Date, cdt As Date) As String
' bdt = birthdate, cdt = date to calc from e.g. current date
Dim d As Double, y As Integer, m As Integer, r As Double
John,

Here is a function that I have used. It could have been done more
efficiently (for example by using the mod function), but it works. To use
the function, reference it in your form, report or query, e.g. in a query:

basAgeCalcExt(dob,date()) as MonthYears [where dob is your date of birth
column and you use date() as the current date to measure against].

Public Function basAgeCalcExt(bdt As Date, cdt As Date) As String
' bdt = birthdate, cdt = date to calc from e.g. current date
Dim d As Double, y As Integer, m As Integer, r As Double
d = (cdt - bdt) / 365 ' calc age in years - no adj for leap year
y = Int(d) ' years as integer
r = d - y ' remainder as decimal
m = Int(r / 0.085) ' rdr months as integer
If y < 1 Then ' show months only
basAgeCalcExt = CStr(m) & " mth"
Else
basAgeCalcExt = CStr(y) & " yr " & CStr(m) & " mth"
End If
End Function

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184

"FishezUk" <fishezuk (AT) btopenworld (DOT) com> wrote