Week number

[rdw456]

Hello all

I have a report that uses a week number in the query to select the records for the week. My week is always Sunday through Saturday ( seven days ) I have a function that up to now has always worked well until this year. What I need is to have week one with any day of the week falling in the new year. Have searched quiet a bit but have been unable to come up with a solution. Would appreciate any thoughts you may have.

Thanks Bob

 

[isladogs]

Try this: Format ([DayDate],"ww")

where DayDate is your calendar date field
For example, this gives value =2 for today's date 13 Jan 2017

 

[Cronk]

I wonder what has changed that your function does not work this year. Did it work the year before last?

Post your function that is not working.

 

[The_Doc_Man]

What I need is to have week one with any day of the week falling in the new year.

This may be the tricky part. Normally, when you do date-oriented formatting, you can add two more arguments that tell you what is the first day of the week (you are not limited to starting on Sunday) and the first week number of the year (you are not limited to 1).

First question: Does your function include the day of week and week of year values or are they blank (implying use of default values)?

Second question: How many years had the function worked before it started giving you incorrect answers?

Third question: How far off were the answers it gave you?

 

[isladogs]

Hi
Maybe I'm missing something but the simple function I suggested does exactly what you want for every year ...
Use Format ([DayDate],"ww") where DayDate is your calendar date field

For example, for the period around the start of 2016, it gives:
DayDate SessionDay CalendarWeekNumber 25/12/2015 Fri 52 26/12/2015 Sat 52 27/12/2015 Sun 53 28/12/2015 Mon 53 29/12/2015 Tue 53 30/12/2015 Wed 53 31/12/2015 Thu 53 01/01/2016 Fri 1 02/01/2016 Sat 1 03/01/2016 Sun 2 04/01/2016 Mon 2 05/01/2016 Tue 2 06/01/2016 Wed 2 07/01/2016 Thu 2 08/01/2016 Fri 2 09/01/2016 Sat 2 10/01/2016 Sun 3
For the same date range in 2016-17, you get:
DayDate SessionDay CalendarWeekNumber 25/12/2016 Sun 53 26/12/2016 Mon 53 27/12/2016 Tue 53 28/12/2016 Wed 53 29/12/2016 Thu 53 30/12/2016 Fri 53 31/12/2016 Sat 53 01/01/2017 Sun 1 02/01/2017 Mon 1 03/01/2017 Tue 1 04/01/2017 Wed 1 05/01/2017 Thu 1 06/01/2017 Fri 1 07/01/2017 Sat 1 08/01/2017 Sun 2 09/01/2017 Mon 2 10/01/2017 Tue 2

Also tested for other years & it always works

 

[isladogs]

Sorry I thought the data above would appear as a table. I hope its still readable

 

[Cronk]

Ridders,

The OP stated "My week is always Sunday through Saturday ( seven days )"

Probably easy for 2017 being Sun Jan 1 2017 to Sat Jan 7 2017

But what about 2018. Is the first week the first week having the first day in it:
Sun Dec 31 2016 to Sat Jan 6,
or the first full week in the year:
Sun Jan 7 to Sat Jan 13

That's the first question. Next is the determination of the date range.

 

[isladogs]

Hi Cronk

In 2017-2018, I get week 1 starting on Mon 1 Jan 2018
31 Dec 2017 is Week 53 for 2017

See selected output below

Day Date Session Day Calendar Week Number
18/12/2017 Mon 51
19/12/2017 Tue 51
20/12/2017 Wed 51
21/12/2017 Thu 51
22/12/2017 Fri 51
23/12/2017 Sat 51
24/12/2017 Sun 52
25/12/2017 Mon 52
26/12/2017 Tue 52
27/12/2017 Wed 52
28/12/2017 Thu 52
29/12/2017 Fri 52
30/12/2017 Sat 52
31/12/2017 Sun 53
01/01/2018 Mon 1
02/01/2018 Tue 1
03/01/2018 Wed 1
04/01/2018 Thu 1
05/01/2018 Fri 1
06/01/2018 Sat 1
07/01/2018 Sun 2
08/01/2018 Mon 2
09/01/2018 Tue 2
10/01/2018 Wed 2
11/01/2018 Thu 2
12/01/2018 Fri 2
13/01/2018 Sat 2
14/01/2018 Sun 3
15/01/2018 Mon 3
16/01/2018 Tue 3
17/01/2018 Wed 3
18/01/2018 Thu 3
19/01/2018 Fri 3
20/01/2018 Sat 3
21/01/2018 Sun 4
22/01/2018 Mon 4
23/01/2018 Tue 4
24/01/2018 Wed 4
25/01/2018 Thu 4
26/01/2018 Fri 4
27/01/2018 Sat 4
28/01/2018 Sun 5

 

[rdw456]

My apologies to every one I went back and checked my data and found the problem is week #53 in my query I group the records by week number so weeks that have a week #53 get missed and in some cases the data is mixed up. There is a bug in access 2003 with the date difference function as reported by microsoft.

So here is what I am trying to do.

1. I have a frm with a textbox that I enter the date

2. I have a query that has a date field formatted to week number

3. I use the week number to filter and group the records

4. I make a field called link to join records in a sub date part (Year) & (Week)


Note

all works until week 53

My weeks always run from Sunday to Saturday


Thanks Ridder for getting me to the root of the problem