Date Query continued from 12/30

M

Mandy J.S.

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



Expand AllCollapse All
 
J

JL

Hi Mandy,

From what you have pasted, you are missing a few ")" like in CDate and
DateAdd.
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:]))

Hope that will help to solve your problem.


Mandy J.S. said:
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...



Expand AllCollapse All
Click to expand...
 
M

Mandy J.S.

That solved the error message, but it's not taking the MAX date from the
tblAttendance.dtmDate. I tried adding in MAX and it told me that it can't
have an aggregate function in a WHERE. They will continue to have dates
added to the attendance table. This is where I enter who was late, left
early or absent. So, I need to it to pull their MAX entry that is before the
end date that I enter and not consider any that I have entered that come
after the end date.

JL said:
Hi Mandy,

From what you have pasted, you are missing a few ")" like in CDate and
DateAdd.
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:]))

Hope that will help to solve your problem.


Mandy J.S. said:
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;

Mandy J.S. wrote:

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



Expand AllCollapse All
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 3
Attendance Query 3
Top 10 in Crosstab query 3
return records from some date and last 30 days 2
Date Calculation from form field 0
selecting records BACK from the maximum date 8
Date Problem Help Needed, Please 14
Problem writing query to get latest date and action from table 6

Top