How to handle DATEPART week 53 in WHERE clause

Hi All,

I have the following SQL statement in MS Access 2010. You will see that I have experienced the weird week 53 issue as per the MS bug.

The issue I have still is that this query expects a week number parameter which is restricted between 1-52.

The following DATEPART within the WHERE clause still creates a week 53 so there are a few records which are being missed:

DatePart("ww",[Patients].DateAdded,2) <= [paramToWeek]

Could anyone suggest how I could fix the WHERE clause so that any [Patients].DateAdded values that fall into week 53 will be returned when the parameter week 1 is passed into the query?


	IIf(DatePart("ww",[Patients].DateAdded,2)>52,1,DatePart("ww",[Patients].DateAdded,2)) AS WeekAdded, 
FROM Patients

((DatePart("yyyy",Patients.DateAdded))=[paramYear]) AND 
( DatePart("ww",[Patients].DateAdded,2) <= [paramToWeek])

ORDER BY DatePart("ww",[Patients].[DateAdded],2);

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First - what's the business rule that would determine what to do with those values that fall into "week 53"? Do you want to include them in the previous year, or should they be included in the "current" year? For example, if the data falls on 12/31/2010 (which was NOT the first full week of 2011) what shoudl Access do with it?

Have you tried altering the last parameter (the "firstweekofyear" parameter)? The one you're using now tells Access to use the first week in the year that has at least 4 days.

DatePart function:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
I always recommend creating your own "calendar" table(s), which will allow you to exactly specify how your business weeks are defined, what your fiscal year is, etc.  It will also vastly simply things like being able to generate a query tallying sales by day, whilst including records for the days where no sales occurred.
rito1Author Commented:

thanks for getting back to me so promptly.

I am trying to sync my query with the week numbers as per our Outlook calendar which for this year the 1st week is 26th Dec 2011 - 1st Jan 2012.

If I just use the default settings for the DatePart function like so - SELECT DatePart("ww",#2011-dec-26#), I get week 53.

My interface which produces a graph based on weeks, requests the the period from and to week values from the user (1-52). What I need is for the crazy 53rd week to be handled as though it is week 1.

If I didn't include parameters and returned all data using the following IIF() statement then I can handle this bug - IIf(DatePart("ww",[Patients].DateAdded,2)>52,1,DatePart("ww",[Patients].DateAdded,2)) AS WeekAdded.

But I don't know how to handle this issue within my WHERE clause.

I hope this makes sense. If you need me to explain anything else then just let me know.


rito1Author Commented:
Excellent thanks. I think I will be going with a manual calendar!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.