Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula question (Access 2000)

    Okay, I should be able to figure this out, but I haven't been able to:
    Trying to create a formula field in an access query that basically gives me length of term of service of an employee, by taking current date (TODAY or NOW?), subtracting hire date(hire date field in the employee table), and then dividing that answer by 365. I have tried several options, but never get the result I want, so apparently I'm writing it wrong.
    Can you help?

    Thanks!

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula question (Access 2000)

    Use the DateDiff() function...
    Try this...
    LengthTOS: DateDiff("d",[HireDate],Date())
    (Substitute your own field names for "length of term of service" and "hire date")

    HTH <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Formula question (Access 2000)

    The current date is Date(). A simple formula is

    LengthOfTerm: (Date()-[HireDate])/365.25

    where HireDate is the name of the field in the employee table. The 365.25 is to take leap years into account. If you want to be absolutely accurate, create a custom function in a standard module:

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

    This function calculates the "age" in years between Date1 and Date2 (Date1 must be before Date2). Use it like this:

    LengthOfTerm: Age([HireDate],Date())

  4. #4
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula question (Access 2000)

    Thanks to both of you for your expert help!

    Tracy

Posting Permissions

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