Get week ending date 1

[Apollo13a]

Hello, I have a query that daily labor hours and with dates for each day. I have this query to get quarter and week numbers. I now want to get the week ending date(always saturday).

SELECT tblContractor.Contractor, tblJobs.JobName, tblHours.ST, tblHours.OT, tblHours.DT, tblHours.Shift, tblHours.WorkDate, DatePart("q",[WorkDate]) AS Cquarter, Format([WorkDate],"ww",2) AS Cweek
FROM (tblContractor INNER JOIN tblJobs ON tblContractor.tblContractorID = tblJobs.Contractor_ID) INNER JOIN tblHours ON tblJobs.tblJobs_ID = tblHours.Jobs_ID
GROUP BY tblContractor.Contractor, tblJobs.JobName, tblHours.ST, tblHours.OT, tblHours.DT, tblHours.Shift, tblHours.WorkDate, DatePart("q",[WorkDate]), Format([WorkDate],"ww",2)
ORDER BY tblHours.WorkDate, DatePart("q",[WorkDate]);

I've checked ms help but...
Can you help?
Jim

 

[dhookom]

You should be able to get a weekending date using an expression like:

  DateAdd("d",-WeekDay([WorkDate])+7,[WorkDate])

Duane
Hook'D on Access
MS Access MVP

 

[Apollo13a]

Perfect. Thanks