Bug in week number query when the year changes

ApplePie

New member
Local time
Today, 07:51
Joined
Feb 28, 2014
Messages
1
I analyse things by the date they are ordered by the office.

The date gets put into a spreadsheet and i import this into access for the querying.

I need to summarise it by weeknumber and year.

I use the following queries:
To get the week: Week Number: DatePart("ww",[DateOrdered],0,2)
To get the year: Year: Year([DateOrdered])
To put it together: Weekyear: [Year] & "_" & [Week Number]

The problem is when the year changes.

The dates for week 1 for 2014 are between 30/12/2013 and 05/01/2014 and therefore straddles 2013 and 2014.

Therefore when the year and week number is put together the following results are given for that particular week:

DateOrdered - Result of above query
30/12/2013 - 2013_1
31/12/2013 - 2013_1
01/01/2014 - 2014_1
02/01/2014 - 2014_1
03/01/2014 - 2014_1
04/01/2014 - 2014_1
05/01/2014 - 2014_1

All of the above should all read 2014_1 but some obviously are 2013_1. This causes me problems when doing further analysis and cross tabulations.

How can I ammend my query so that the above all show the correct year and week number: 2014_1 ?

Many thanks,
AP
 
Year: Year([DateOrdered])
+ IIF(Month(dateordered) = 12 and DatePart("ww",[DateOrdered],0,2) = 1,1,0)
- IIF(Month(dateordered) = 1 and DatePart("ww",[DateOrdered],0,2) = 52,1,0)
- IIF(Month(dateordered) = 1 and DatePart("ww",[DateOrdered],0,2) = 53,1,0)
 
I haven't spent the time to figure it myself, but does namlians solution work for 12/31/2000? That date is in the 54th week of 2000.
 
Stricktly speaking there never is accoording to proper weeknumbering a week number 54 and only 1/4 years or so have 53 weeks, not every year.
 
Oh goodie, we get to discuss again your theoretical world and the one that we actually deal with.

I'm game. Run this and tell me what you get:

DatePart("ww", "12/31/2000")
 
A Microsoft bug? :D

12/31/2001 - 53
12/31/2002 - 53
12/31/2003 - 53
12/31/2004 - 53
12/31/2005 - 53


Ooh :eek:

12/31/1916 - 54
12/31/1944 - 54
12/31/1972 - 54

12/31/2000 - 54
12/31/2028 - 54
12/31/2056 - 54
12/31/2084 - 54

12/31/2124 - 54
12/31/2152 - 54
12/31/2180 - 54

12/31/2220 - 54
...

But 7 * 52 = 364 days (0 - 51 / 1 - 52)
and 7 * 53 = 371 days (0 - 52 / 1 - 53)

How the HELL can there be 54 weeks?

Hmm, if weeks start on a Sunday and 1st Jan was on a Saturday it might cause something ...

Year 2000 started on a Saturday and ended on a Sunday. There were 53 Sundays plus an odd Saturday (1st Jan).
 
Last edited:
I have since long time ago used the code below - don't remember where I got it:
Code:
Public Function getWeekNumber(year, mon, day) As Integer
    Dim a, y, m, JD, d4, L, d1
    a = (14 - mon) \ 12
    y = year + 4800 - a
    m = mon + 12 * a - 3
    JD = day + (153 * m + 2) \ 5 + 365 * y + y \ 4 - y \ 100 + y \ 400 - 32045
    d4 = (((JD + 31741 - JD Mod 7) Mod 146097) Mod 36524) Mod 1461
    L = d4 \ 1460
    d1 = ((d4 - L) Mod 365) + L
    getWeekNumber = d1 \ 7 + 1
End Function

Public Function getWeekNumberFromDate(MyDate As Date) As Integer
    Dim myYear As Integer
    Dim myMonth As Integer
    Dim myDay As Integer
    myYear = DatePart("yyyy", MyDate)

    myMonth = DatePart("m", MyDate)
    myDay = DatePart("d", MyDate)
    getWeekNumberFromDate = getWeekNumber(myYear, myMonth, myDay)
End Function
 
Last edited:
Exactly nanscombe. When we try to fit the square peg of weeks into the round hole of years things get weird when you know where to look.

365/7 equals 52 Remainder 1, meaning a 365 day year will always span exactly 53 weeks.

366/7 equals 52 Remainder 2, meaning a year can sometimes span 54 weeks.

The keyword in both those sentences is 'span'.
 
it isn't a bug. vba/access has several alternatives to determine exactly which week constitutes week1 of a given year.

you may still have a situation that doesn't fit the access/vba model

look at help for datepart, format or similar functions.


----
based on this, without testing, I can see that plog may be right, it would need the definition of week1 to include only jan1st of the current year, and then dec 31st of a leap year, would fall in week 54, I assume.
 
Last edited:
Oh goodie, we get to discuss again your theoretical world and the one that we actually deal with.
The real world I actually deal with has 52 and 53 weeks, never does it have 54 weeks and only rarely does it have 53
 

Users who are viewing this thread

Back
Top Bottom