The following query returns dates >=155.
What I would like is to return results for the last 4 weeks instead. I could use "Quarter" but that will be affected at the year end.
So it is best to return the last 4 weeks data regardless of the year. I tried the datepart function but nothing is returned.
Code: Select all
SELECT DBA_JOB_CONTROL_VIEW.JobID, DBA_JOB_CONTROL_VIEW.VehicleMake, DBA_JOB_CONTROL_VIEW.VehicleModel, DBA_JOB_CONTROL_VIEW.VehicleReg, DBA_JOB_CONTROL_VIEW.JobDateOutExpected AS ECD, IIf(Not IsNull([CompletionDate]),DateValue([CompletionDate])) AS Completed, DatePart("ww",[Completed]) AS Week
FROM DBA_JOB_CONTROL_VIEW
WHERE (((IIf(Not IsNull([CompletionDate]),DateValue([CompletionDate])))>=Date()-155))
ORDER BY DBA_JOB_CONTROL_VIEW.JobDateOutExpected;
Thanks