Formula question (Access 2000)

[pribb]

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!

[Alexya1]

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>

[HansV]

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())

[pribb]

Thanks to both of you for your expert help!

Tracy