Week Commencing - but records only have dates 1

[Stevehewitt]

Hi Guys,

I need to create a report. Each record in the table that this report is to be based on has a date stamp of when it was entered into the database.

The report will be showing various totals of the records in the database.

The problem is that management want the report to show the totals in week commencing columns. Of course the database doesn't know when weeks begin, although each record is has a date stamp.

Any ideas how to incorporate it so that the report "understands" when a week commences?

Thanks,


Steve

 

[PaulBricker]

If you want the date the week starts (Sunday) you can use
[DateField] - Weekday([DateField])+1

That will return the Sunday of the week that you date is in. You could group on that expression to get weekly totals.
For today's date that would be April 10, 2005
Or you can use
DatePart("ww",[Datefield])

This will return the week of the year that your date falls into and you could group on that.
For todays date that would be 16

Depends on how you want this to go.

Paul

 

[PaulBricker]

Let me restate this. Your report should be based on a query. In a new field in that query, you can use the expression
MyGroupDate:[DateField]-Weekday([DateField])+1

Then in your report, you can GroupBy the field MyGroupDate, which will give you the Week Starting Date, and put your DateField in the Detail section.

This will return your dates Grouped in the way you want. Then you can do your total based on a weekly value.

Paul

 

[Stevehewitt]

Ah!
I just got a notification email whilst typing my reply!

Thanks Paul, I'm current involved with something else for the next couple of hours, but I'll post back and let you know how I got on.

Cheers for your help,

Star is in the post...!

Ta,


Steve

 

[Stevehewitt]

Hi again Paul,

Thank you very much. Worked like a treat! Superb.

Thank again,


Steve.

 

[PaulBricker]

Glad to help and thanks for the star.


Paul