Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Age in years & age in days (2003)

    Hi,

    I have two fields of employee (date of birth field, hiring date) in my database. Now I need create two fields to calcalate the age in years at hiring date & age in days at hiring date (coded only when the age in years is less than 1).

    So what's the best way to get the data in those new two fields?

    Thanks in advance.

    Regards,

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

    Re: Age in years & age in days (2003)

    You could use this custom VBA function:

    Function Age(Bdate, DateToday) As Integer
    ' Returns the Age in years between 2 dates
    ' Doesn't handle negative date ranges i.e. Bdate > DateToday
    Age = Year(DateToday) - Year(Bdate)
    If Month(DateToday) < Month(Bdate) Or _
    (Month(DateToday) = Month(Bdate) And _
    Day(DateToday) < Day(Bdate)) Then
    Age = Age - 1
    End If
    End Function

    You can use it in a query:

    AgeYears: Age([Date of Birth],[Hiring Date])

    How could someone's age be less than 1? Are you hiring newborn babies?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age in years & age in days (2003)

    Thanks, Hans.

    The age in years is working, but not age in days. What should I put in Age in days field in a query? If the age in years is great than 1, then the age in days field is blank. If the age in years is less than 1, then the age in days field will show the days between Bdate and DateToday.

    Thanks again.

    Regards,

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

    Re: Age in years & age in days (2003)

    AgeDays: IIf([AgeYears]<1,[Hiring Date]-[Date of Birth],Null)

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Age in years & age in days (2003)

    Thank you, Hans.

Posting Permissions

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