Age Function (MS Access 2003)

Topic

New Reply

I need to calculate an age value but with a twist. I need to have anyone under 2 years to have a value in months with an “M” character attached. For everyone else just a year number is fine. Since I will have a DOB (Date of Birth) my thought is to do the calculation first in months, then everyone 24 or less add an “M” and then convert the others to years. That seems a bit convoluted. Any suggestions?

Reply | Quote

Replies

You could put this function in a module and use it in a query or in the control source of a text box:

Function ShowAge(ByVal varDOB) As String
Dim lngYears As Long
Dim lngMonths As Long
Dim lngWeeks As Long
Dim lngDays As Long
If IsNull(varDOB) Then
Exit Function
End If
If Not IsDate(varDOB) Then
Exit Function
End If
If Date < CDate(varDOB) Then
Exit Function
End If
varDOB = CDate(varDOB)
lngYears = Year(Date) – Year(varDOB)
If Month(Date) < Month(varDOB) Or Month(Date) = _
Month(varDOB) And Day(Date) 1 Then
ShowAge = lngYears
Else
lngMonths = DateDiff(“m”, varDOB, Date)
If Day(Date) < Day(varDOB) Then
lngMonths = lngMonths – 1
End If
ShowAge = lngMonths & "M"
End If
End Function

Use it as

MyAge: ShowAge([DateOfBirth])

or

=ShowAge([DateOfBirth])

Reply | Quote

Try this:
age: IIf(DateDiff(“m”,[DOB],Date())>24,Format(Date()-[DOB],”yy”),”M” & DateDiff(“m”,[DOB],Date()))

By calculating as required you don’t need to store the age in the database (redundant date) you can calculate from todays date and DOB

John

Reply | Quote

John,

Your expression works well in most cases, but sometimes it returns an incorrect answer. For example, for a baby born on the 31st of March 2006, it returns M2, and for a child born on the 16th of May 2001, it returns 04.

Reply | Quote

Thanks Hans
I guess it requires a little refining – sometimes it’s handy to have the calculation as one expression, rather than having to run a routine – never as elegant as your solutions
John

Reply | Quote

As noted you could use a query expression rather than user-defined function to perform this calculation, but the expression will need to be somewhat more convoluted. Attached db file (A2K format) shows an example, see query qry_GetAge, which uses either method to calculate age in years or months, or either depending on person’s age. Note that if calculating months only, you can format results to include an “M” using format string “#M” (the backslash “escapes” the “M” as a literal), and still use the resulting numbers in calculations (see Age Months (Formatted)); but when combining years (number) and months (formatted text) in same calculated field (Age Years Or Months (Expr)), only the numerical results will be usable in calculation, as shown by Test Calc column. Do not know of a way to apply more than one format to a single field in query. This is expression I used:

Age Years Or Months (Expr): IIf(IIf(Date()24,IIf(Date()<DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate])),DateDiff("yyyy",[BirthDate],Date())-1,DateDiff("yyyy",[BirthDate],Date())),Format(IIf(Date()<DateSerial(Year(Date()),Month(Date()),Day([BirthDate])),DateDiff("m",[BirthDate],Date())-1,DateDiff("m",[BirthDate],Date())),"#M"))

The db uses copy of Northwind “Employees” table (with some dummy records added) to test query. Generally a query that uses expressions using only built-in VBA functions will open quicker that equivalent query that uses user-defined VBA functions. The logic is easier to follow in a function, but with small sets of records the difference in query execution time will be negligible.

HTH

Reply | Quote