YearWeek trouble

  • Thread starter Thread starter Sandy Eastman
  • Start date Start date
S

Sandy Eastman

Using Access 2000 on Win2000Pro

I have a date field in my data and I need to group and sort summarized data
by YearWeek but the format() function is not doing what I need it to do.
The format function defaults the first week of the year as the week
containing Jan 1 and weeks are not returned as 2 digits.

How can I get a YearWeek that is accurate and can be used to sort.


using Format([REQUIRED_BY_DATE],"yyyyww",1,2)

1st problem:
you dont get 2 digit week numbers
Jan 5, 2004 is returned as 20041
.... so you cant use this to accurately sort the data.

2nd Problem:
Jan 1, 2004 is returned as 200453 instead of 200353
.... so again you can't sort properly.

I can get around the 2 digit week problem with:
Format([REQUIRED_BY_DATE],"yyyy",1,2) &
Format(Format([REQUIRED_BY_DATE],"ww",1,2),"00")
....but the year is still wrong for Jan 1, 2004
 
Check it out ...run this query:

SELECT
DatePart("yyyy",#1/1/2004#,1,2) & "wk" &
Format(DatePart("ww",#1/1/2004#,1,2),"00")
AS YearWeek;

....You get "2004wk53" ...not quite right (should be "2003wk53")

Am I going to have to write something to check
if month = 1 and week >52, then year = year-1 ???

There has to some way to get the right year, where the first week has 4
days...
(

Dan Artuso said:
Hi,
Have you looked at using the DatePart function instead?

HTH
Dan Artuso, MVP

Using Access 2000 on Win2000Pro

I have a date field in my data and I need to group and sort summarized data
by YearWeek but the format() function is not doing what I need it to do.
The format function defaults the first week of the year as the week
containing Jan 1 and weeks are not returned as 2 digits.

How can I get a YearWeek that is accurate and can be used to sort.


using Format([REQUIRED_BY_DATE],"yyyyww",1,2)

1st problem:
you dont get 2 digit week numbers
Jan 5, 2004 is returned as 20041
... so you cant use this to accurately sort the data.

2nd Problem:
Jan 1, 2004 is returned as 200453 instead of 200353
... so again you can't sort properly.

I can get around the 2 digit week problem with:
Format([REQUIRED_BY_DATE],"yyyy",1,2) &
Format(Format([REQUIRED_BY_DATE],"ww",1,2),"00")
...but the year is still wrong for Jan 1, 2004
Click to expand...
Click to expand...
 
Back
Top