>= 10 years query.

[Guest]

I have a database with current employees. I need to find out out of all those
employees, who has been with the company >= 10 years. How do I write the
query for years for the hire_date field in the query designer?

Thanks!

 

[Guest]

Hi,
In the query design grid, write the following in the Field box:
DateDiff("y",[hire_date],Date())
and then type >9 in the Criteria box below.
Hope this helps,
Lee

 

[John Spencer (MVP)]

Minor correction

DateDiff("yyyy",[Hire_Date],Date())

That will give you the number of year boundaries crossed, but not the number of years.

Personnally, I would use criteria like the following if you needed to just
return those people that have been with the company for 10 years or more as of
todays date

Field: Hire_Date
Criteria: < DateAdd("yyyy",-10,Date())

If you wanted some date other than today's date then put that into place where
Date() is.

If you need to calculate years of service and display it, then use a calculated
formula. The second part of the statement adjusts the year count by -1 if the
month and day of hiredate are later than the month and day of the current date

Field: DateDiff("yyyy",[Hire_Date],Date()) + Format([HireDate],"mmdd") > Format(Date(),"mmdd")

Check the formula (part 2) as I sometimes get the comparison backwards.

Hi,
In the query design grid, write the following in the Field box:
DateDiff("y",[hire_date],Date())
and then type >9 in the Criteria box below.
Hope this helps,
Lee

I have a database with current employees. I need to find out out of all those
employees, who has been with the company >= 10 years. How do I write the
query for years for the hire_date field in the query designer?

Thanks!