Week Number and Date Part

[Alexander Willey]

Hello,

I have a simple sales database in Access 2013 with various date fields
I need to produce queries and reports using week numbers

When I use Format([DateField],"ww") Access gives me week numbers that are 1 week ahead of my business calendar week numbers, and they don't match up.

I understand the way around this is to use Date Part, however I have read that there is a Bug around weeks 53 and week 1, for some particular years

My database includes dates from 2015, until say 2020, when it will probably have been replaced by something else

What is the easiest and safest way to go?. Should I avoid Date Part in view of the bug or is it fairly minor and therefore the best way to go, given I only have a basic level of programming knowledge and so would struggle with a high-end solution

Appreciate any help or tips

Many thanks in advance

Kind regards

Alex

 

[jdraw]

You might want to look at the FirstWeekOfYear option within the Format function, one of the options may be more applicable to your situation.

Good luck.

 

[arnelgp]

will creating a public function in a module help you:
on your query:

WeekNo: fnWeekNum([DateField])

Public Function fnWeekNum(Optional ByVal dt As Date = 0) As Integer

Dim tmpDate As Date
Dim i As Integer
Dim yr As Integer
If dt = 0 Then dt = Date
yr = Year(dt)
i = 1
tmpDate = DateSerial(yr, 1, i)
While tmpDate < dt
i = i + 1
tmpDate = DateSerial(yr, 1, i)
Wend
fnWeekNum = i \ 7
If (i Mod 7) > 0 Then fnWeekNum = fnWeekNum + 1

End Function

 

[pbaldy]

I switched from using week numbers in part because of this (also because I wanted something year-independent). I calculate a week ending date and use that in place of week number.

[DateField]+(7-Weekday([DateField],1)) AS WeekEndDate

 

[plog]

First, I don't think there's a bug with Access weeks, there's a bug in people who don't understand division. In most years there are 52.1429 weeks. Some years have 52.2857 weeks. So, every year there is a fractional week left over. Over time when you try to fit a 52 square weeks into a whole round year you get additional days that throw off your Weeks to Years comparisons. Those days have to go somewhere. That's why some years span 54 weeks (54 is not a typo).

This is going to happen no matter what system you use or when you start your weeks/years. Its inherent into this imprecise conversion you are attempting.

With that said, I would explicitly declare Day 1, Week 1, Year 1 of your database. You said you are getting a week behind and your data started in 2015. What i would do is create function and use January 8, 2015 as the first day to compare all other dates to. You would pass this function a date, it would do a DateDiff calculation on 1/8/2015 and the date you passed it and would return the differences in weeks between those 2 dates.

Again, though, that mehtod isn't perfect and none will be because you are doing an imperfect comarison.

 

[arnelgp]

you can also use excel's WeekNum function.
just make a reference to Microsoft Excel X.XX Object:

Public Function ExcelWeekNum(d As Date) As Integer
    ExcelWeekNum = Excel.WorksheetFunction.WeekNum(d)
End Function

on your query:

WeekNum: ExcelWeekNum([DateField])

 

[Alexander Willey]

Thank you All
arnelgp. I managed to get your suggested public function in a module to work, however the first day of the week appears to be Friday, whereas I need it to be Mondays. For example I need Monday 06/06/2016 through to Friday 10/06/2016 to be in week 23, whereas Friday 10/06/2016 is currently falling in to week 24. Any ideas how to do achieve this, using your public function?
Thank you very much

thanks again all for your help

 

[arnelgp]

here i modify your code to suit your need.

just call it with parameters DateField, the firstday of week start, how many weeks to adjust:

fnWeekNum([DateField], vbMonday, -1)

Public Function fnWeekNum(Optional ByVal dt As Date = 0, _
                          Optional FirstDayOfWeek As VbDayOfWeek = vbSunday, _
                          Optional AdjustByHowManyWeeks As Integer = 0) As Integer

    Dim tmpDate As Date
    Dim i As Integer, j As Integer
    Dim yr As Integer
    If dt = 0 Then dt = Date
    yr = Year(dt)
    i = 1: j = 1
    tmpDate = DateSerial(yr, 1, i)
    While tmpDate <= dt
        'If (Format(tmpDate, "dddd") = "Sunday") And (i > 1) Then j = j + 1
        If (Weekday(tmpDate) = FirstDayOfWeek) And (i > 1) Then j = j + 1
        i = i + 1
        tmpDate = DateSerial(yr, 1, i)
    Wend
    fnWeekNum = j + AdjustByHowManyWeeks
End Function

 

[JHB]

First, I don't think there's a bug with Access weeks, there's a bug in people who don't understand division. In most years there are 52.1429 weeks. Some years have 52.2857 weeks. So, every year there is a fractional week left over. Over time when you try to fit a 52 square weeks into a whole round year you get additional days that throw off your Weeks to Years comparisons. Those days have to go somewhere. That's why some years span 54 weeks (54 is not a typo).

There is a bug, which is confirmed by Microsoft.
https://support.microsoft.com/en-us/kb/200299

 

[Simon_MT]

Financial Periods or Weeks differ from pure Calendar based calculations. I create a Week Master with Start and End date and lookup via VBA using the transaction date for the Week Number, in some cases, Period No and then Financial Year. First day of the year is in late March or early April.

Simon