Week numbers

  • Thread starter Thread starter Art Vandaley
  • Start date Start date
A

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.
 
One method (there are others)

week:
IIf(Format([Table1]![Date1],"w")=1,Format([Table1]![Date1],"ww"),Format([Table1]![Date1],"ww")+1)
 
Art said:
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?
Click to expand...

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)
 
Back
Top