Correct Week Number

[Dave Spence]

I have a field that calculates a week number based on a
scheduled start date that is included in the the data. I
have it formatted so that I get a 4 digit return (i.e.
0501). The problem is that this is returning this week as
0502 instead of 0501. We use the rule of the first Thur
of the year as week 1. Any help would be appreciated. The
calculated field in the query is below.

SchedWeek#: (Format(Format(dbo_tpo_dss_jobtask!
sched_start_date,"yy"),"00"))+(Format(Format
(dbo_tpo_dss_jobtask!sched_start_date,"ww"),"00"))

 

[JohnFol]

Format(dbo_tpo_dss_jobtask!sched_start_date,"ww")
should be
Format(dbo_tpo_dss_jobtask!sched_start_date,"ww", vbThursday)

 

[Dave Spence]

When I tried adding the vbThursday, I recieved the error
message below. I've included how I modified the query.
Thanks
------------------
This expression is typed incorrectly, or it is too
complex to be evaluated. For example, a numeric
expression may contain too many complicated elements. Try
simplifying the expression by assigning parts of the
expression to variables. (Error 3071)

Modified Query:
SchedWeek#: (Format(Format([dbo_tpo_dss_jobtask]!
[sched_start_date],"yy","vbThursday"),"00"))+(Format
(Format([dbo_tpo_dss_jobtask]!
[sched_start_date],"ww","vbThursday"),"00"))

 

[Brendan Reynolds]

You can't use intrinsic constants such as vbThursday in queries, only in
code. In the query, you'll need to use the numeric value of the constant. To
find the numeric value, you can look it up in the help file or Object
Browser, or just open the Immediate window (Ctrl+G) and type ? followed by
the constant name, e.g. ? vbThusday.
--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.