Anniversery Dates

E

Ernst Guckel

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.
 
M

Marshall Barton

Ernst said:
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)
Click to expand...


1) Your years of service calculation is incorrect. See
http://www.mvps.org/access/datetime/date0001.htm
for a precise method.

2) If you are not interested in the time of day, use the
Date() function instead of Now()

3) You can not use a field's alias name outside the select
clause (i.e. YearsOfService). Instead, you need to repeat
the calculated field's expression.

4) Aliad names do not always work inside the select clause.
It's more likely to be accepted if you put the calculated
field earlier in the list.

I have no idea what a flag date is, but maybe the where
clause should be:

WHERE DateDiff("d", Date(), dateOfHire) Between 0 And 30
 
R

raskew via AccessMonster.com

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 said:
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.
Click to expand...
 
Top