Re: Anniversery Dates
- From: "raskew via AccessMonster.com" <u28575@uwe>
- Date: Sat, 29 Mar 2008 23:43:40 GMT
The following example, based on Northwind's Employees table will return those
employees whose birthday or hiring anniversary falls within the next 90 days.
To use, copy to a new query, replace table and field names as appropriate,
and change the references to 90 to the number of days you want to capture.
SELECT
Employees.LastName
, Employees.FirstName
, DateSerial(Year(Date())+IIf(Format([birthdate],"mmdd")<Format(Date(),
"mmdd"),1,0),Month([birthdate]),Day([birthdate])) AS dteBDay
, DateSerial(Year(Date())+IIf(Format([hiredate],"mmdd")<Format(Date(),
"mmdd"),1,0),Month([hiredate]),Day([hiredate])) AS dteHDay
FROM
Employees
WHERE
(((DateSerial(Year(Date())+IIf(Format([birthdate],"mmdd")<Format(Date(),
"mmdd"),1,0),Month([birthdate]),Day([birthdate]))) Between Date()
AND
Date()+90))
OR
(((DateSerial(Year(Date())+IIf(Format([hiredate],"mmdd")<Format(Date(),
"mmdd"),1,0),Month([hiredate]),Day([hiredate]))) Between Date()
AND
Date()+90));
HTH - Bob
Ernst Guckel wrote:
Hello,
This seems to be a problem but I cannot figure out why...
SELECT qryEmpDates.EmpFirstLast,
DateAdd("yyyy",[YearsOfService],[dateOfHire]) AS EmpAnniversery,
DateDiff("yyyy",[DateOfHire],Now()) AS YearsOfService,
[EmpAnniversery]-GetSetting("EventFlag") AS FlagDate
FROM qryEmpDates
WHERE (((DateAdd("yyyy",[YearsOfService],[dateOfHire]))>Now()));
If I remove the criteria of > now() it displays everyone's anniversery date
but I only want the ones within the flagdate (30 days)
Ernst.
--
Message posted via http://www.accessmonster.com
.
- References:
- Anniversery Dates
- From: Ernst Guckel
- Anniversery Dates
- Prev by Date: Re: Union query and exclusion of data
- Next by Date: Re: Adding data in a 2nd query
- Previous by thread: Re: Anniversery Dates
- Next by thread: Union query and exclusion of data
- Index(es):
Relevant Pages
|
