Week number wrong

[Nikki17]

I am extracting some information from my database in week format.
I have done this in the normal way of using the Format function and W.
The week number it gives me is one ahead of actual week number in my diary.
Has anyone else come across this, or know how to fix it??

[John Spencer]

The week number is dependent on two factors - what is the first day of the
week and how is the first week defined. Take a look at the help on the
format function. You may need to use

Format("ww",SomeDate,2,3)

That says return the week number for some date where the week starts on
Monday and week number 1 of the year is the first week with seven days. If
you didn't set those arguments, then your week number is based on Sunday
and week number 1 is the week that contains January 1.

[Jeff Boyce]

Perhaps your Windows settings specify a different start day for the year
than you are using?

--
Regards

Jeff Boyce
<Office/Access MVP>

[Emma Hope]

Simply speaking you could write your formulae and put -1 at the end, i.e.
minus 1 from each week number to give the correct value.

[Nikki17]

Thanks John that works. I don't understand what the 3 does though. I am
assuming the 2 is to start the day on Monday? Could you confirm what the 2,
and 3 relate to?

"John Spencer" wrote:

The week number is dependent on two factors - what is the first day of the
week and how is the first week defined. Take a look at the help on the
format function. You may need to use

Format("ww",SomeDate,2,3)

That says return the week number for some date where the week starts on
Monday and week number 1 of the year is the first week with seven days. If
you didn't set those arguments, then your week number is based on Sunday
and week number 1 is the week that contains January 1.

[John Spencer]

The 2 argument is Monday
The 3 argument is Week 1 of the year has seven days in the year.

For an explanation, look them up in VBA help.
-- Open up your application
-- Type Control G
-- Press the F1 key
--Click the Index tab
--Type Format Function in the Keywords entry
--Click on Search
--Click on Format Function in the Choose a Topic list

OR
-- Open up your application
-- Type Control G
-- Press the F1 key
-- Click on the Contents tab
-- Select Visual Basic Language Reference: Functions:D-G: Format Function

[Schwagr]

Thanks John & Nikki; adding the '3' argument was all I needed to solve my problem with weeks 53 ('05) and 1 ('06) overlapping which I posted this morning!

Schwagr