25 & 35 Years Service

[Pete]

I would like to pick out the Employees that have 25 or 35 years
service on or before the 31st Dec each year. I have their startdate in
my query

thanks in advance

Peter

 

[John Spencer]

SELECT *
FROM Table
WHERE Year([ServiceDate]) In (Year(Date())-25), Year(Date())-35))

In the query grid
Field: TheYear: Year([TableName].[ServiceDate])
Criteria: In (Year(Date())-25), Year(Date())-35)

That can be made more efficient if you have a very large number of
employees to check.

WHERE [ServiceDate] Between DateSerial(Year(Date())-25,1,1) and
DateSerial(Year(Date())-25,12,31)
OR
[ServiceDate] Between DateSerial(Year(Date())-35,1,1) and
DateSerial(Year(Date())-35,12,31)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Jeff Boyce]

Peter

So, you're saying you want to know if the difference between their start
date and December 31st of the current year is greater than or equal to 25
years. And again, >=35 years...

Take a look at the DateSerial() function (you'll use something like):
DateSerial(Year(Date()), 12, 31)
to get December 31st of the current year.

Take a look at the DateDiff() function (you'll use something like):
DateDiff("y",[StartDate],DateSerial(.... see above...))

You might need to mess with the syntax and order (which date first) - see
Access HELP.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Pete]

Peter

So, you're saying you want to know if the difference between their start
date and December 31st of the current year is greater than or equal to 25
years. And again, >=35 years...

Take a look at the DateSerial() function (you'll use something like):
DateSerial(Year(Date()), 12, 31)
to get December 31st of the current year.

Take a look at the DateDiff() function (you'll use something like):
DateDiff("y",[StartDate],DateSerial(.... see above...))

You might need to mess with the syntax and order (which date first) - see
Access HELP.

--
Regards

Jeff Boyce
Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Microsoft Registered Partnerhttps://partner.microsoft.com/

I would like to pick out the Employees that have 25 or 35 years
service on or before the 31st Dec each year. I have their startdate in
my query

thanks in advance

Peter- Hide quoted text -

- Show quoted text -

Thanks, the DateDiff function did the trick, much appreciated