Incorrect "yyyy" for "ww" when using DatePart

[Guest]

I have a table of data for which each record has a date and an amount of lost
time. I am trying to group the total lost time into each week for a each
year. I have created the following query to do this:

SELECT [Lost Time].Date, DatePart("yyyy",[Date],2,2) & " " &
DatePart("ww",[Date],2,2) AS [Year Week], [Lost Time].[Minutes Lost] AS
[Total Lost Time], [Lost Time].Date
FROM [Lost Time]
WHERE ((([Lost Time].Date)>#12/31/1998#))
ORDER BY [Lost Time].Date;

What i have noticed is that, if we take week 52 2005 as an example, because
the 1st of Jan 2006 was actually in week 52 of 2005 my "Year Week" column
actually reads "52 2006", when it should be grouped with "52 2005".

Does anyone have any suggestions as to how i can overcome this problem,
preferably being able to keep the "Year Week" column...?

I hope this makes sense, please let me know if you would like me to explain
further.

Many thanks in advance

 

[Gary Walter]

I have a table of data for which each record has a date and an amount of
lost
time. I am trying to group the total lost time into each week for a each
year. I have created the following query to do this:

SELECT [Lost Time].Date, DatePart("yyyy",[Date],2,2) & " " &
DatePart("ww",[Date],2,2) AS [Year Week], [Lost Time].[Minutes Lost] AS
[Total Lost Time], [Lost Time].Date
FROM [Lost Time]
WHERE ((([Lost Time].Date)>#12/31/1998#))
ORDER BY [Lost Time].Date;

What i have noticed is that, if we take week 52 2005 as an example,
because
the 1st of Jan 2006 was actually in week 52 of 2005 my "Year Week" column
actually reads "52 2006", when it should be grouped with "52 2005".

Does anyone have any suggestions as to how i can overcome this problem,
preferably being able to keep the "Year Week" column...?

My best advice (truly) is to create a table "Calendar"

You can do it many ways, but if you go into Excel
you can easily create a column of dates (65K+)
in less than 2 minutes.

Import the date column into Access Calendar table.
Add all the extra fields you can think you might need
(like "WeekYear")

Run update queries to define those extra fields.

Physically change any values that don't match
your business rules.

Plus, your queries will "zip" without all the VBA functions.

And, this extra table is very low "cost" to your app.

 

[Guest]

Stephen,

Best do as Gary suggests, he's just helped me a lot.

But if you want to do it inside the query then consider using the Iif
function (nested if necessary). In English-speak want you want to achieve is:
'If the month is 1 and the week of year is greater than 5 then subtract 1 for
the year number.' I can't think of a case where a January date would be in
week 6.

No grouping in your SQL?

Regards,

Rod