Grouping by Week, but displaying week commencing date

[JasChima]

I have a query of records that have to be grouped by week, i cant figure out how to display the week commencing date instead of the week of the year. The date I want to show is of the monday of the week.

Field name im using is 'Date'

Can anyone suggest code to correct this problem?

 

[meboz]

Create a query with a field that returns the date of the Monday immediately before the 'Date' field

SELECT Table1.date, Weekday([date]) AS WeekDayNumber, [date]-([WeekDayNumber]-2) AS MondayPrior
FROM Table1;

Also, inspect the DateGrouping property in Reports

 

[JasChima]

Nice one Meboz, it worked a treat!