Query to report Week Number

[unclefink]

I am working on a database which will generate a report for data based on week numbers. Everything is going good so far with an exception to one problem I am having.

When a year starts or ends in the middle of a week, its throwing the data off for that one week when referencing the week number.

Example: The first full week of 2013 is December 30-January 5, 2013. When I run the query now that has a calculated field in it to give me week number. The problem with this, is when a new week is started in the prior year its starting a 53rd week.

I want to be able to generate a report for three years comparing week to week between the three years. When the year ends in the middle of the week, how do I make the data for the 53rd week include itself for the first week of the next year in situatuions such as this.

We need data for the 52 full weeks.

I am currently using the following criteria to generate the week number.
WeekNumber: Format([CaseDate],"ww")

Any help is appreciated.

 

[plog]

I don't think this is going to work because there aren't exactly 52 weeks in a year. Eventually, those 1-2 days are going to add up and you are going to be forced to put that 53rd week somewhere.

I actually wrote this code:

Function getWeekNum(d)
    ' takes date and determines what week of year it falls on, based on Week 0 of Year 0 starting 12/27/2011
    
ret = DateDiff("ww", "12/27/2011", d)
    ' determines total number of weeks since 12/27/2011
    
ret = (ret Mod 52) + 1
    ' if over 1 year from 12/27/11 it forces it to be number from 1-52
    
getWeekNum = ret
    ' returns total number of weeks regardless of year

    
End Function

It calculates what you want based on a start date of 12/27/2011. Doing that means week 52 ends on 12/22/2012 and week 53 starts on 12/23/2012. That means week 53 becomes week 1, however that means week 2 starts 12/30/2012.

This isn't going to work unless you account for those additional days.

 

[JHB]

Hi unclefink, to calculate the correct weeknumber has always been a pain (not only in Access), because you can use "vbMonday" (the week's first day) and "vbFirstFourDays" (when a date connect to this or next week), and still there are some years where dates is not in the correct week (example 31-12-2007).

Then I came across the below function in the Internet (function written by George Simms). After using this function my entire weeknumber problem was gone, maybe it can help you too.

Function WEEKNR(InputDate As Long) As Integer
Dim A As Integer, B As Integer, C As Long, D As Integer
    WEEKNR = 0
    If InputDate < 1 Then Exit Function
    A = Weekday(InputDate, vbSunday)
    B = Year(InputDate + ((8 - A) Mod 7) - 3)
    C = DateSerial(B, 1, 1)
    D = (Weekday(C, vbSunday) + 1) Mod 7
    WEEKNR = Int((InputDate - C - 3 + D) / 7) + 1
End Function