Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Age Function (MS Access 2003)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Age Function (MS Access 2003)

    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) < Day(varDOB) Then
    lngYears = lngYears - 1
    End If
    If lngYears > 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])

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age Function (MS Access 2003)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Age Function (MS Access 2003)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age Function (MS Access 2003)

    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

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Age Function (MS Access 2003)

    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:

    <code> Age Years Or Months (Expr): IIf(IIf(Date()<DateSerial(Year(Date()),Month(Date( )),Day([BirthDate])),DateDiff("m",[BirthDate],Date())-1,DateDiff("m",[BirthDate],Date()))>24,IIf(Date()<DateSerial(Year(Date()),Mo nth([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")) </code>

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •