• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1602
  • Last Modified:

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

0
rito1
Asked:
rito1
  • 2
2 Solutions
 
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:
http://office.microsoft.com/en-us/access-help/datepart-function-HA001228812.aspx
0
 
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.
0
 
rito1Author Commented:
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
0
 
rito1Author Commented:
Excellent thanks. I think I will be going with a manual calendar!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now