Getting date of week ending

[jalldridge]

I'm running a query that pull all records for a specified month.

The report that I'm creating groups this information on a lastName basis, and for each lastName a group is set up on a weekly basis (this grouping obviously being on the date field in the query)

Now for each week I want to create a heading that is along the lines of:

Week ending XXX (where xxx is the date of the last sunday in that week).

I'm stumped as to how to get this information? Pointers appreciated

Hope thats clears

 

[jalldridge]

Additional confusion...

I've put a text box in my date_header and have added the following code to the control source:

=WeekdayName(Weekday([date],2)) & "," & [date]

as you will see in the detail section in the attached image, one section starts on a saturday and the other starts on a monday.

It look like when access does the grouping, the last day of the week is a saturday which is correct. However the detail section seems to to have the monday as the start day? Hence the sunday data is in the wrong section.

Ideally the saturday data should be in its own group, and the sunday and monday in the next.

Thanks

 

[jalldridge]

OK cracked getting the last day in the week...

Didnt realise that you could set a text box control source to a function that returned a string. As soon as I found this, then I was able to do the maths to work out the necessary date.

However still am totally lost about why the sunday is classed as the last day in the detail section

 

[jalldridge]

Solved this one... The report wizard had added extra groupings to my report which were causing the issues

 

[DanG]

As a side issue..
You may not want to group on "LastName" if there are two lastnames that are the same they will be grouped together.
You are better off to group on ID (a unique field)

 

[jalldridge]

Good point thanks for that