Re: Anniversery Dates



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

.



Relevant Pages

  • RE: Storing birthdate with year optional
    ... Note that in years which are not a leap year the expression would evaluate ... to True on 1 March for anyone with a birthday on 29 February. ... Optional varDateAt As Variant) As Boolean ... year toy could list all employees whose birthday is today in a query along ...
    (microsoft.public.access.gettingstarted)
  • Re: Excel to notify date of birth
    ... (column on worksheet blinking in any colour) ... A blinking column would require the use of a timer and would be a load ... message box saying the employees name or something like that. ... and determine if the birthday is within 3 days prior of the current ...
    (microsoft.public.excel.programming)
  • Re: OT Stranger than fiction...
    ... >>> chances of someone having the same birthday as you in a room with ... their birthday off. ... Just in case 2 employees in a workgroup had birthdays on the same date ... In my 5-person workgroup THREE of us shared the same date. ...
    (rec.outdoors.rv-travel)
  • How do I send birthday reminders to certain contacts?
    ... I am using 2003 outlook. ... all birthdays for employees. ... Reminders have been set for 2 weeks. ... that an employee's birthday is such an such date. ...
    (microsoft.public.outlook.general)
  • RE: birthday reminder
    ... a birthday report to know who's birthday is today ... with criteria -- ... I have a list of employees with a list of birthdays, ...
    (microsoft.public.access.forms)

Quantcast