Calculate weeknumber based upon non-default startdate (1 Viewer)

[ino_mart]

All

I need to write a query which retrieves the weeknumber from the datefield.

The datefield is in the format dd/mmm/yyyy hh:nn

There is however one oddity: a new week starts at Friday evening 22:00 hour.

Friday 28th of December 2012 22:00 was weeknumber 1.
Weeknumber 2 started at Friday 4th of January 2013 at 22:00.
Weeknumber 3 started at Friday 11th of January 2013 at 22:00.
...

The weeknumber is reset to 1 on last Friday before New year at 22:00. In case New Year is a Friday, the week number is set to 1 on New Year at 22:00. With other words: the weeknumber is set to 1 on Friday 27th of December 2013 at 22:00 .

If the datefield contains 11/Jan/2013 21:59 it is to be handled as week 2 as it is not yet 22:00 hours. If a datefield contains 11/Jan/2013 22:01 the weeknumber is 3.

Anyone a suggestion how to do this in a query?
The records are each week imported via some VBA-code. If there is no solution in the query, I can add an extra field in my table to store this weeknumer. So the weeknumber can be calculated via VBA-code during import.

 

[John Big Booty]

Have a look at the DatePart() function you would need something along the lines of;

(DatePart("w", [YourDateField], 6, 6)

You would just need to do an adjustment to date where the time was between 22:00 and 00:00 hours.