Week Number

[Ed]

Hi All: I had the following qry field working fine until 2005 came up.
My league started November 3rd so I set the formula to: "Week:
(Format([Date],"ww",2))-44" which gave me week "1" since league started 44
weeks into year. But what now? any way to have calc continue to work into 05
returning consecutive weeks from 04? It went from week 9 to week -43.
Thanks, Ed

 

[Dirk Goldgar]

Hi All: I had the following qry field working fine until 2005 came up.
My league started November 3rd so I set the formula to: "Week:
(Format([Date],"ww",2))-44" which gave me week "1" since league
started 44 weeks into year. But what now? any way to have calc
continue to work into 05 returning consecutive weeks from 04? It went
from week 9 to week -43. Thanks, Ed

So you want to calculate the "week number" as the number of weeks since
the league started, counting the week of 11/03/2004 as week 1? I'm not
sure, but maybe this will give you what you want:

Week: DateDiff("ww", #11/3/04#, Date) + 1

 

[Ed]

Thanks, Looks like it may work but problem is week starts on tuesday. Doesnt
seem to return proper number yet. Could you explain the function you
proposed above?
Ed

Hi All: I had the following qry field working fine until 2005 came up.
My league started November 3rd so I set the formula to: "Week:
(Format([Date],"ww",2))-44" which gave me week "1" since league
started 44 weeks into year. But what now? any way to have calc
continue to work into 05 returning consecutive weeks from 04? It went
from week 9 to week -43. Thanks, Ed

So you want to calculate the "week number" as the number of weeks since
the league started, counting the week of 11/03/2004 as week 1? I'm not
sure, but maybe this will give you what you want:

Week: DateDiff("ww", #11/3/04#, Date) + 1

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks, Looks like it may work but problem is week starts on tuesday.
Doesnt seem to return proper number yet. Could you explain the
function you proposed above?

Did you look in the online help? All the details are there.

If your week starts on Tuesday, you can adjust for that:

Week: DateDiff("ww", #11/3/04#, Date, 3) + 1

Try that and see whether it comes out the way you want.

 

[Ed]

Thanks again: sure did lookup but does not explain each section of function.
Running winxp pro, office xp dev.
Will look deeper but does not offer to much on way of each part of
expressions for that matter.
Ed