Date query

[Guest]

Hello,

I am trying to create a query that will show me all of the employees
eligible for attendance bonus for a certain month. The criteria is that they
must have 90 days perfect attendance prior to the end of the month. Right
now I have it so that it is >Date()-90 and this works if I print it out on
the 1st day of the month, but if I need to reprint it during the month it is
incorrect. How can I input the last day of the month? It keeps coming up
too complicated to calculate. I tried >[Enter End Date]-90. We tried
reprinting for November and it was incorrect because some people have now
become eligible that weren't. This also goes by their anniversary date.
Their anniversary date must also be 90 days prior to the end of that month.
So, if someone was here for 90 days and had perfect attendance they would get
a bonus. Thanks for your help.

 

[Guest]

I went back into my query here's how it reads:

for the incident date: <=Date()-90

for the anniversary date: <=Date()-120

for current employee: yes

for salaried employee: no (only hourly are eligible)

So, someone has to work 30 days to get into the bonus program and then 90
days perfect attendance after that to get a bonus.

I just thought I'd clarify how I have this set up.

 

[Ken Snell [MVP]]

Paste the full SQL statement of the query that you're currently using. I
don't understand exactly what you're wanting to do when you say "how can I
input the last day of the month"? Which month? the month when you're running
the query? The month before the month when you're running the query?

Please give a few examples of what you expect to happen and give a few data
examples as well.

Also, when you tried the

[Enter End Date]-90

what did you enter for the date value (something like 12/25/2004, perhaps)?

--

Ken Snell
<MS ACCESS MVP>

I went back into my query here's how it reads:

for the incident date: <=Date()-90

for the anniversary date: <=Date()-120

for current employee: yes

for salaried employee: no (only hourly are eligible)

So, someone has to work 30 days to get into the bonus program and then 90
days perfect attendance after that to get a bonus.

I just thought I'd clarify how I have this set up.

Hello,

I am trying to create a query that will show me all of the employees
eligible for attendance bonus for a certain month. The criteria is that
they
must have 90 days perfect attendance prior to the end of the month.
Right
now I have it so that it is >Date()-90 and this works if I print it out
on
the 1st day of the month, but if I need to reprint it during the month it
is
incorrect. How can I input the last day of the month? It keeps coming
up
too complicated to calculate. I tried >[Enter End Date]-90. We tried
reprinting for November and it was incorrect because some people have now
become eligible that weren't. This also goes by their anniversary date.
Their anniversary date must also be 90 days prior to the end of that
month.
So, if someone was here for 90 days and had perfect attendance they would
get
a bonus. Thanks for your help.

 

[Guest]

Here's the full SQL statement of what I am currently using.
SELECT tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
Max(tblAttendance.dtmDate) AS MaxOfdtmDate, tblEmployees.ynCurrentEmp,
tblEmployees.ynSalariedEmp, tblEmployees.dtmAnniveraryCompany
FROM tblIncidentType RIGHT JOIN (tblEmployees LEFT JOIN tblAttendance ON
tblEmployees.numIDNumber = tblAttendance.numID) ON
tblIncidentType.strIncidentType = tblAttendance.strIncidentType
GROUP BY tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
tblEmployees.ynCurrentEmp, tblEmployees.ynSalariedEmp,
tblEmployees.dtmAnniveraryCompany
HAVING (((Max(tblAttendance.dtmDate))<=Date()-90) AND
((tblEmployees.ynCurrentEmp)=Yes) AND ((tblEmployees.ynSalariedEmp)=No) AND
((tblEmployees.dtmAnniveraryCompany)<=Date()-120))
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;


The date that I want to input is the last day of the month that I want to
look at. Say I want to see who all was eligible for the attendance bonus in
November, but it is now December 20th. I want to input November 30, 2004 and
get the employees that were to receive the bonus.

Yes, the date that entered was 11/30/2004.

Paste the full SQL statement of the query that you're currently using. I
don't understand exactly what you're wanting to do when you say "how can I
input the last day of the month"? Which month? the month when you're running
the query? The month before the month when you're running the query?

Please give a few examples of what you expect to happen and give a few data
examples as well.

Also, when you tried the

[Enter End Date]-90

what did you enter for the date value (something like 12/25/2004, perhaps)?

--

Ken Snell
<MS ACCESS MVP>

I went back into my query here's how it reads:

for the incident date: <=Date()-90

for the anniversary date: <=Date()-120

for current employee: yes

for salaried employee: no (only hourly are eligible)

So, someone has to work 30 days to get into the bonus program and then 90
days perfect attendance after that to get a bonus.

I just thought I'd clarify how I have this set up.

Hello,

I am trying to create a query that will show me all of the employees
eligible for attendance bonus for a certain month. The criteria is that
they
must have 90 days perfect attendance prior to the end of the month.
Right
now I have it so that it is >Date()-90 and this works if I print it out
on
the 1st day of the month, but if I need to reprint it during the month it
is
incorrect. How can I input the last day of the month? It keeps coming
up
too complicated to calculate. I tried >[Enter End Date]-90. We tried
reprinting for November and it was incorrect because some people have now
become eligible that weren't. This also goes by their anniversary date.
Their anniversary date must also be 90 days prior to the end of that
month.
So, if someone was here for 90 days and had perfect attendance they would
get
a bonus. Thanks for your help.

 

[Ken Snell [MVP]]

OK -

Try replacing
Date()-90
with this:
DateAdd("d", -90, CDate([Enter the ending date:]))

And replace
Date()-120
with this:
DateAdd("d", -120, CDate([Enter the ending date:]))


--

Ken Snell
<MS ACCESS MVP>

Here's the full SQL statement of what I am currently using.
SELECT tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
Max(tblAttendance.dtmDate) AS MaxOfdtmDate, tblEmployees.ynCurrentEmp,
tblEmployees.ynSalariedEmp, tblEmployees.dtmAnniveraryCompany
FROM tblIncidentType RIGHT JOIN (tblEmployees LEFT JOIN tblAttendance ON
tblEmployees.numIDNumber = tblAttendance.numID) ON
tblIncidentType.strIncidentType = tblAttendance.strIncidentType
GROUP BY tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
tblEmployees.ynCurrentEmp, tblEmployees.ynSalariedEmp,
tblEmployees.dtmAnniveraryCompany
HAVING (((Max(tblAttendance.dtmDate))<=Date()-90) AND
((tblEmployees.ynCurrentEmp)=Yes) AND ((tblEmployees.ynSalariedEmp)=No)
AND
((tblEmployees.dtmAnniveraryCompany)<=Date()-120))
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;


The date that I want to input is the last day of the month that I want to
look at. Say I want to see who all was eligible for the attendance bonus
in
November, but it is now December 20th. I want to input November 30, 2004
and
get the employees that were to receive the bonus.

Yes, the date that entered was 11/30/2004.

Paste the full SQL statement of the query that you're currently using. I
don't understand exactly what you're wanting to do when you say "how can
I
input the last day of the month"? Which month? the month when you're
running
the query? The month before the month when you're running the query?

Please give a few examples of what you expect to happen and give a few
data
examples as well.

Also, when you tried the

[Enter End Date]-90

what did you enter for the date value (something like 12/25/2004,
perhaps)?

--

Ken Snell
<MS ACCESS MVP>

I went back into my query here's how it reads:

for the incident date: <=Date()-90

for the anniversary date: <=Date()-120

for current employee: yes

for salaried employee: no (only hourly are eligible)

So, someone has to work 30 days to get into the bonus program and then
90
days perfect attendance after that to get a bonus.

I just thought I'd clarify how I have this set up.


:

Hello,

I am trying to create a query that will show me all of the employees
eligible for attendance bonus for a certain month. The criteria is
that
they
must have 90 days perfect attendance prior to the end of the month.
Right
now I have it so that it is >Date()-90 and this works if I print it
out
on
the 1st day of the month, but if I need to reprint it during the month
it
is
incorrect. How can I input the last day of the month? It keeps
coming
up
too complicated to calculate. I tried >[Enter End Date]-90. We tried
reprinting for November and it was incorrect because some people have
now
become eligible that weren't. This also goes by their anniversary
date.
Their anniversary date must also be 90 days prior to the end of that
month.
So, if someone was here for 90 days and had perfect attendance they
would
get
a bonus. Thanks for your help.