Group by Week Crossing Year End

[KMKelly]

I need to set up a report to group information by week for a 52 full week (Sunday - Saturday) period. In the past I have had issues doing grouping by week at the point where the year ends.

With months, you can do a Format(DateField, "yyyymm") that can sort November, December, January, February... even though the months would be 11, 12, 1, 2... With weeks, I tried the same thing, but the portion of the whole week (Sunday - Saturday) that ends in December would be 200453 (for example) and the first week in January would be 200501, but they are the same week, because neither is a full week. The sort order would work, but that week is broken into two.

Does anyone have a fix for this problem that they have already done? Thanks.

 

[PHV]

You may try something like this:
Format(DateSerial(Year(DateField), Month(DateField),1+Day(DateField)-WeekDay(DateField)),"yyyyww")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hwkranger]

You can also build mapping tables, map dates to each week. I've done this before. It may not seem to be the "best" solution for you, but in a normalized reporting database this has been optimal for me.

Randall Vollen
National City Bank Corp.