Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Difference in years (2002)

    Hi
    I need this equation in a Query to give me the date in years only, not years with a decimal point and 10 or so digits after it, I guess I want an absolute value.
    This is what I have so far, any improvements appreciated. Dates are currently set at short
    Age[test_results].[date tested]-[Patient].[DateOfBirth])/365 & " years"

    Cheers
    Turniphead

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Difference in years (2002)

    Take a look at the DateDiff dfunction in Access Help.
    Also this link to the Access Web might be of use (particularly calculating the Age of someone)

    HTH.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Date Difference in years (2002)

    You could use the Int function to truncate the decimal part (the 365.25 is to take leap years into account)

    Age:Int([test_results].[date tested]-[Patient].[DateOfBirth])/365.25) & " years"

    Or you could use the following complicated expression:

    Age:Year([test_results].[date tested])-Year([Patient].[DateOfBirth])-IIf(DateSerial(2000;Month([test_results].[date tested]);Day([test_results].[date tested]))<DateSerial(2000;Month([Patient].[DateOfBirth]);Day([Patient].[DateOfBirth])),1,0)

    Or you could create a custom function in a module:

    Function AgeDiff(Date1, Date2) As Integer
    ' Returns the Age in years between 2 dates
    AgeDiff = Year(Date2) - Year(Date1)
    If Month(Date2) < Month(Date1) Or (Month(Date2) = Month(Date1) And Day(Date2) < Day(Date1)) Then
    AgeDiff = AgeDiff - 1
    End If
    End Function

    and use the expression

    Age:AgeDiff([Patient].[DateOfBirth],[test_results].[date tested])

  4. #4
    New Lounger
    Join Date
    Aug 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Difference in years (2002)

    Thanks everyone, I'm sorted now

    Turniphead

Posting Permissions

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