asked on

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?

Thanks,

Rit

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

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

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

Open in new window

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rito1

ASKER

Hi,

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.

Thanks,

Rit

rito1

ASKER

Excellent thanks. I think I will be going with a manual calendar!