If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
25 & 35 Years Service
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 |
#2
|
|||
|
|||
25 & 35 Years Service
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 '================================================= === Pete wrote: 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 |
#3
|
|||
|
|||
25 & 35 Years Service
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 http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Pete" wrote in message ups.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 |
#4
|
|||
|
|||
25 & 35 Years Service
On 21 Apr, 21:09, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote: 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/ "Pete" wrote in message ups.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 |
Thread Tools | |
Display Modes | |
|
|