Dates this quarter

[D Willett]

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

Can anyone assist?

Thanks

[HansV]

If you want the last 4 weeks you could use Date()-28 instead of Date()-155

[D Willett]

Hi Hans
Confused... I thought Date()-28 would return 28 days of data??
Cheers

[HansV]

"What I would like is to return results for the last 4 weeks instead."

Uh - aren't 4 weeks equivalent to 28 days?

[D Willett]

Oh yes.................... sorry brain not in gear ....

Cheers Hans