Previous week if first of the year

[skwilliams]

I have some queries which pull data from the previous week using the DatePart function for the week and year which always works fine. My problem is when the week is the first week of the year. Any help is greatly appreciated.

Here's my SQL

SELECT DISTINCT tblReprints.EmpName, tblReprints.Area, tblReprints.Order, tblReprints.Parts, tblReprints.Line, tblReprints.Ct, DatePart("ww",[dtDate]) AS Wk, DatePart("yyyy",[dtDate]) AS Yr
FROM tblReprints
WHERE (((DatePart("ww",[dtDate]))=DatePart("ww",Date())-1) AND ((DatePart("yyyy",[dtDate]))=DatePart("yyyy",Date())))
ORDER BY tblReprints.Area, tblReprints.Order;

 

[apr pillai]

Try with this change:

SELECT DISTINCT tblReprints.EmpName, tblReprints.Area, tblReprints.Order, tblReprints.Parts, tblReprints.Line, tblReprints.Ct, DatePart("ww",[dtDate]) AS Wk, DatePart("yyyy",[dtDate]) AS Yr
FROM tblReprints
WHERE (((DatePart("ww",[dtDate]))=DatePart("ww",Date())-1) AND ((DatePart("yyyy",[dtDate]))=DatePart("yyyy",[B][I]Date()-7[/I][/B])))
ORDER BY tblReprints.Area, tblReprints.Order;