Date Query

G

Guest

I have a query where I am trying to input the date I want it to stop looking
for attendance points. Here's how I want to work it:

I want to print the attendance bonus results for the Month of November at
some point in the month of December. The day they are printed each month is
not going to be the same date. I may even want to go back further then just
one month. The problem I am having is that even though I am telling it what
end date I want it to end at. It isn't ending at that date. So, if someone
got points after the end date that I input they are still being used against
them. Here is the sql that I have for it:

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))<=DateAdd("d",-90,CDate([Enter the
ending date:]))) AND ((tblEmployees.ynCurrentEmp)=Yes) AND
((tblEmployees.ynSalariedEmp)=No) AND
((tblEmployees.dtmAnniveraryCompany)<=DateAdd("d",-120,CDate([Enter the
ending date:]))))
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;

Thank you for any help.
 
J

John Spencer (MVP)

The problem is possibly due to you using a HAVING clause instead of a WHERE
clause. It could also be that you need to have both a where clause and a having
clause in your SQL.

WHERE clauses select the records based before the aggregation (summing) occurs,
HAVING clauses act after the aggregation. If you are using the query grid, the
way to apply WHERE criteria is to add the relevant field(s) to the grid a second
time, select WHERE from in the TOTAL cell and then enter the appropriate criteria.

A GUESS on what you are attempting to do might be the following.

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
WHERE tblAttendance.dtmDate <=
DateAdd("d",-90,CDate([Enter the ending date:]
AND tblEmployees.ynCurrentEmp=True
AND tblEmployees.ynSalariedEmp=False
AND tblEmployees.dtmAnniveraryCompany <=
DateAdd("d",-120,CDate([Enter the ending date:])
GROUP BY tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
tblEmployees.ynCurrentEmp, tblEmployees.ynSalariedEmp,
tblEmployees.dtmAnniveraryCompany
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;
I have a query where I am trying to input the date I want it to stop looking
for attendance points. Here's how I want to work it:

I want to print the attendance bonus results for the Month of November at
some point in the month of December. The day they are printed each month is
not going to be the same date. I may even want to go back further then just
one month. The problem I am having is that even though I am telling it what
end date I want it to end at. It isn't ending at that date. So, if someone
got points after the end date that I input they are still being used against
them. Here is the sql that I have for it:

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))<=DateAdd("d",-90,CDate([Enter the
ending date:]))) AND ((tblEmployees.ynCurrentEmp)=Yes) AND
((tblEmployees.ynSalariedEmp)=No) AND
((tblEmployees.dtmAnniveraryCompany)<=DateAdd("d",-120,CDate([Enter the
ending date:]))))
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;

Thank you for any help.
Click to expand...
 
G

Guest

I am getting a missing operator message for the

tblAttendance.dtmDate <=DateAdd("d",-90,CDate([Enter the ending date:]
AND tblEmployees.ynCurrentEmp=True
AND tblEmployees.ynSalariedEmp=False
AND tblEmployees.dtmAnniveraryCompany <=DateAdd("d",-120,CDate([Enter the
ending date:])

portion of it Any ideas? Thanks for your help. I was off of work after I
sent this out originally, which is why it took so long to test it.

John Spencer (MVP) said:
The problem is possibly due to you using a HAVING clause instead of a WHERE
clause. It could also be that you need to have both a where clause and a having
clause in your SQL.

WHERE clauses select the records based before the aggregation (summing) occurs,
HAVING clauses act after the aggregation. If you are using the query grid, the
way to apply WHERE criteria is to add the relevant field(s) to the grid a second
time, select WHERE from in the TOTAL cell and then enter the appropriate criteria.

A GUESS on what you are attempting to do might be the following.

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
WHERE tblAttendance.dtmDate <=
DateAdd("d",-90,CDate([Enter the ending date:]
AND tblEmployees.ynCurrentEmp=True
AND tblEmployees.ynSalariedEmp=False
AND tblEmployees.dtmAnniveraryCompany <=
DateAdd("d",-120,CDate([Enter the ending date:])
GROUP BY tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
tblEmployees.ynCurrentEmp, tblEmployees.ynSalariedEmp,
tblEmployees.dtmAnniveraryCompany
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;
I have a query where I am trying to input the date I want it to stop looking
for attendance points. Here's how I want to work it:

I want to print the attendance bonus results for the Month of November at
some point in the month of December. The day they are printed each month is
not going to be the same date. I may even want to go back further then just
one month. The problem I am having is that even though I am telling it what
end date I want it to end at. It isn't ending at that date. So, if someone
got points after the end date that I input they are still being used against
them. Here is the sql that I have for it:

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))<=DateAdd("d",-90,CDate([Enter the
ending date:]))) AND ((tblEmployees.ynCurrentEmp)=Yes) AND
((tblEmployees.ynSalariedEmp)=No) AND
((tblEmployees.dtmAnniveraryCompany)<=DateAdd("d",-120,CDate([Enter the
ending date:]))))
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;

Thank you for any help.
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Yes. I seem to have left off a closing parentheses or two. In both cases right
after the parameter [Enter the Ending Date]

tblAttendance.dtmDate <=DateAdd("d",-90,CDate([Enter the ending date:])
AND tblEmployees.ynCurrentEmp=True
AND tblEmployees.ynSalariedEmp=False
AND tblEmployees.dtmAnniveraryCompany <=DateAdd("d",-120,CDate([Enter the ending date:]))

I am getting a missing operator message for the

tblAttendance.dtmDate <=DateAdd("d",-90,CDate([Enter the ending date:]
AND tblEmployees.ynCurrentEmp=True
AND tblEmployees.ynSalariedEmp=False
AND tblEmployees.dtmAnniveraryCompany <=DateAdd("d",-120,CDate([Enter the
ending date:])

portion of it Any ideas? Thanks for your help. I was off of work after I
sent this out originally, which is why it took so long to test it.

John Spencer (MVP) said:
The problem is possibly due to you using a HAVING clause instead of a WHERE
clause. It could also be that you need to have both a where clause and a having
clause in your SQL.

WHERE clauses select the records based before the aggregation (summing) occurs,
HAVING clauses act after the aggregation. If you are using the query grid, the
way to apply WHERE criteria is to add the relevant field(s) to the grid a second
time, select WHERE from in the TOTAL cell and then enter the appropriate criteria.

A GUESS on what you are attempting to do might be the following.

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
WHERE tblAttendance.dtmDate <=
DateAdd("d",-90,CDate([Enter the ending date:]
AND tblEmployees.ynCurrentEmp=True
AND tblEmployees.ynSalariedEmp=False
AND tblEmployees.dtmAnniveraryCompany <=
DateAdd("d",-120,CDate([Enter the ending date:])
GROUP BY tblEmployees.numIDNumber, tblEmployees.strLastName,
tblEmployees.strMiddleName, tblEmployees.strFirstName,
tblEmployees.ynCurrentEmp, tblEmployees.ynSalariedEmp,
tblEmployees.dtmAnniveraryCompany
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;
I have a query where I am trying to input the date I want it to stop looking
for attendance points. Here's how I want to work it:

I want to print the attendance bonus results for the Month of November at
some point in the month of December. The day they are printed each month is
not going to be the same date. I may even want to go back further then just
one month. The problem I am having is that even though I am telling it what
end date I want it to end at. It isn't ending at that date. So, if someone
got points after the end date that I input they are still being used against
them. Here is the sql that I have for it:

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))<=DateAdd("d",-90,CDate([Enter the
ending date:]))) AND ((tblEmployees.ynCurrentEmp)=Yes) AND
((tblEmployees.ynSalariedEmp)=No) AND
((tblEmployees.dtmAnniveraryCompany)<=DateAdd("d",-120,CDate([Enter the
ending date:]))))
ORDER BY tblEmployees.numIDNumber, Max(tblAttendance.dtmDate) DESC;

Thank you for any help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Query continued from 12/30 2
Attendance Query 3
cross tab confusion 2
Date Query 4
Date Expressions & Calculations 3
Crosstab Query for Beginning Date and Ending Date 11
Show maximum date in a query/report - JCW 4
Year to Date Query Issue 2

Top