Week numbers

[Art Vandaley]

Hi,

I have a problem with my query which shows week numbers on a column. Week
numbers derived from date field of table. Column has below string:

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

Above equation gives next week number for Sundays. On my computer date
system is normally adjusted to start the new week number on Mondays.

At the other hand I have a string calculating week number for my form like:

WEEK.Value = Format(Date1.Value, "ww", vbMonday, vbFirstFourDays)

Above formula gives correct result. For example:

24th. February 2008 ==> My form gives 8 and my query gives 9 for week
number.

Does anybody have an idea?

Best Regards.

 

[Wayne-I-M]

One method (there are others)

week:
IIf(Format([Table1]![Date1],"w")=1,Format([Table1]![Date1],"ww"),Format([Table1]![Date1],"ww")+1)

 

[James A. Fortune]

Hi,

I have a problem with my query which shows week numbers on a column. Week
numbers derived from date field of table. Column has below string:

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

Above equation gives next week number for Sundays. On my computer date
system is normally adjusted to start the new week number on Mondays.

At the other hand I have a string calculating week number for my form like:

WEEK.Value = Format(Date1.Value, "ww", vbMonday, vbFirstFourDays)

Above formula gives correct result. For example:

24th. February 2008 ==> My form gives 8 and my query gives 9 for week
number.

Does anybody have an idea?

Art,

You've already answered your own question! The Expression Service in
Access' queries do not know about the named constants you are using in
your form code. In the query, try replacing the constants with their
integral values (in QBE):

WEEK: Format([Table1].[Date1], "ww", 2, 2)

or

vbMonday: 2
vbFirstFourDays: 2
WEEK: Format([Table1].[Date1], "ww", vbMonday, vbFirstFourDays)

or in SQL:

SELECT 2 AS vbMonday, 2 AS vbFirstFourDays, Format([Table1].[Date1],
"ww", vbMonday, vbFirstFourDays) AS WEEK, ...

James A. Fortune
(e-mail address removed)