DateAdd function

  • Thread starter Thread starter Hey
  • Start date Start date
H

Hey

I have a report that should pick up individuals that have been absent
for 3-8 weeks. It should base this off the last date attended. I
formerly had "last" in the group field but it missed many records so I
changed to "max" and the correct individuals show. But when I add this
criteria in my [Date] field, I drop from 2846 records to 48. It is
omitting most of the records. Is there something wrong here?

<=DateAdd("ww",-3,Date()) And >=DateAdd("ww",-8,Date())

Here is the full statement if that helps:

SELECT tmpAttendance.PersID, Max(tmpAttendance.Date) AS MaxOfDate,
tmpAttendance.ClassID, tmpAttendance.Present, tblClasses.ClassName
FROM tmpAttendance INNER JOIN tblClasses ON tmpAttendance.ClassID =
tblClasses.ClassID
GROUP BY tmpAttendance.PersID, tmpAttendance.ClassID,
tmpAttendance.Present,
tblClasses.ClassName
HAVING (((tmpAttendance.ClassID)=28 Or (tmpAttendance.ClassID)=1 Or
(tmpAttendance.ClassID)=2 Or (tmpAttendance.ClassID)=3 Or
(tmpAttendance.ClassID)=4 Or (tmpAttendance.ClassID)=19 Or
(tmpAttendance.ClassID)=20))
ORDER BY tmpAttendance.PersID, Max(tmpAttendance.Date);
 
Try sorting the table by Date and seeing what the answer should be. Maybe
48 is correct.
 
Hey said:
I have a report that should pick up individuals that have been absent
for 3-8 weeks. It should base this off the last date attended. I
formerly had "last" in the group field but it missed many records so I
changed to "max" and the correct individuals show. But when I add this
criteria in my [Date] field, I drop from 2846 records to 48. It is
omitting most of the records. Is there something wrong here?

<=DateAdd("ww",-3,Date()) And >=DateAdd("ww",-8,Date())

Here is the full statement if that helps:

SELECT tmpAttendance.PersID, Max(tmpAttendance.Date) AS MaxOfDate,
tmpAttendance.ClassID, tmpAttendance.Present, tblClasses.ClassName
FROM tmpAttendance INNER JOIN tblClasses ON tmpAttendance.ClassID =
tblClasses.ClassID
GROUP BY tmpAttendance.PersID, tmpAttendance.ClassID,
tmpAttendance.Present,
tblClasses.ClassName
HAVING (((tmpAttendance.ClassID)=28 Or (tmpAttendance.ClassID)=1 Or
(tmpAttendance.ClassID)=2 Or (tmpAttendance.ClassID)=3 Or
(tmpAttendance.ClassID)=4 Or (tmpAttendance.ClassID)=19 Or
(tmpAttendance.ClassID)=20))
ORDER BY tmpAttendance.PersID, Max(tmpAttendance.Date);
Click to expand...


I'm not sure Hey, but I think your query might be mixed up
on the use of Where and Having. After shortening the
ClassID conditions, I think it should be more like:

SELECT tmpAttendance.PersID,
Max(tmpAttendance.Date) AS MaxOfDate,
tmpAttendance.ClassID,
tmpAttendance.Present,
tblClasses.ClassName
FROM tmpAttendance INNER JOIN tblClasses
ON tmpAttendance.ClassID = tblClasses.ClassID
WHERE tmpAttendance.ClassID IN(1,2,3,4,19,20,28)
GROUP BY tmpAttendance.PersID,
tmpAttendance.ClassID,
tmpAttendance.Present,
tblClasses.ClassName
HAVING Max(tmpAttendance.Date) Between
DateAdd("ww", -8, Date()) And DateAdd("ww", -3, Date())
ORDER BY tmpAttendance.PersID, Max(tmpAttendance.Date)
 
Back
Top