Show week beginning on a report

[Crawforda]

Hi

I am creating a monthly report of product outputs grouped by members of staff but with a week by week breakdown.
My problem is that if a member of staff doesn't have any outputs on Monday, then the date on the report is set to the first instance (whenever in that week he does have some output). Is there a way of setting the date to the Monday of the week in question.
The report is created from a query which selects the required month. The report then groups the data into member of staff level then into week number.

Any help would be greatly apprecieated.

Anthony

 

[Kage]

Place the following in a text box in the date header and it will group the report by week number (i.e. the week of 2/3/2002-2/9/2002 will be listed as week 6).

=Format([Date],"ww")

Hope this helps....

 

[Crawforda]

That's what I've had to resort to, but I'd really like it if it would display 4/3/2002 as that is the Monday of that week.
Perhaps I should modify the question to:
How can I display the date of the Monday on my report when it is grouped into weeks?
Or how can I convert =Format([date],"ww") into the specific date on each Monday?

Any further help would be appreciated

 

[Kage]

For some reason, this issue was driving me crazy...so I came home and tried again with a fresh mind. Try this in the Date Header field:

=IIf(Weekday([Dt])=7,[Dt]-5,IIf(Weekday([Dt])=6,[Dt]-4,IIf(Weekday([Dt])=5,[Dt]-3,IIf(Weekday([Dt])=4,[Dt]-2,IIf(Weekday([Dt])=3,[Dt]-1,IIf(Weekday([Dt])=2,[Dt],[Dt]+1))))))

Please note that if you have a field dated on Sunday, it will be grouped with the following Monday data.

Let me know if this works.

Good luck!!

 

[Crawforda]

You are a genius. That code works an absolute treat.

Thank you ever so much for all your help.

Anthony