Week number calculation

  • Thread starter Thread starter Henning
  • Start date Start date
H

Henning

I am at ny ends wit, trying to figure out how to calculate
the current week number in current year and display the
result in an unbound tect box on a form.

I have tried the following
=weeknum(now(),2)
but it wil not work.

I am running access 2000 on an XP machine.

Any bright spark will be appriciated.

Henning
 
I am at ny ends wit, trying to figure out how to calculate
the current week number in current year and display the
result in an unbound tect box on a form.

I have tried the following
=weeknum(now(),2)
but it wil not work.

I am running access 2000 on an XP machine.
Click to expand...

Try:

=datepart("ww",date())
 
I am at ny ends wit, trying to figure out how to calculate
the current week number in current year and display the
result in an unbound tect box on a form.

I have tried the following
=weeknum(now(),2)
but it wil not work.

I am running access 2000 on an XP machine.

Any bright spark will be appriciated.

Henning
Click to expand...

=DatePart("ww",Date())
 
=DatePart("w",Date(),2,2)

The first number indicates the first day of the week - use 1 for Sunday, 2
for Monday etc.
The second number indicates the first week of the year.
1 = Start with week in which January 1 occurs (default).
2 = Start with the first week that has at least four days in the new year -
European Default

Cheers,
dullish spark!
 
I am at ny ends wit, trying to figure out how to calculate
the current week number in current year and display the
result in an unbound tect box on a form.

I have tried the following
=weeknum(now(),2)
but it wil not work.
Click to expand...

This is the most generally reliable VBA expression for determining the
week number according to ISO standards. Specify all the optional
parameters to make sure user settings don't trip you up.

CInt(Format(TheDate, "ww", vbMonday, vbFirstFourDays))

Unfortunately, a bug makes Format() return the wrong value for some
days; 29 Dec 2003, for example. A description of the bug
is in KB article Q200299, "Format or DatePart Functions Can Return
Wrong Week Number". That article includes a function that works
correctly (according to ISO 8601).

Using a table instead of a function might make your application more
useful. A table containing the week numbers for every day for the
next 100 years would have less than 37,000 rows. It can be used
directly by any client software, not just Access. And using a table
makes it dead simple to determine the dates covered by a given week
number.
 
Back
Top