Wrong week number

[Art Vandaley]

Hi,

(Access 2007)

I have a query with an expression in a field like:

WEEK: Format$([Table1].[Date1];'ww')

Above expression is always giving next week's week number if the day is
Sunday. What can it be the problem. Any idea?

Best Regards.

 

[Steve Schapel]

Art,

No, this is not wrong. The date formats use Sunday as the first day of
the week by default. If you want it to use another day, you need to
specify. Also, drop the $. To start the week on Monday, try it like this:

WEEK: Format([Table1].[Date1];'ww';2)

 

[Guillermo_Lopez]

Hi,

(Access 2007)

I have a query with an expression in a field like:

WEEK: Format$([Table1].[Date1];'ww')

Above expression is always giving next week's week number if the day is
Sunday. What can it be the problem. Any idea?

Best Regards.

The default for dates is to use Sunday as the first day of the week.
So if its sunday, it belongs on the following week. You can specify
you First day though.

WEEK: Format$([Table1].[Date1];'ww';vbMonday) where vbMonday = 2. This
will turn: Sun, Mon, Tue, Wed, Thu, Fri, Sat week into a Mon, Tue,
Wed, Thu, Fri, Sat, Sun week. Hope this works.

You can also use the following function:

WEEK: DatePart('ww',[Table1].[Date1],vbMonday)

PS.: This is for Access 2003 but it should be the same on 2007.
-GL