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

My WHERE Clause Using DatePart is Not Working the Way I Need it to

I am using the following WHERE clause to attemp to pull only data from LAST WEEK from a table

WHERE DatePart("ww", [15th_St_Panel_Plant_FPY.ProductionDate]) = (DatePart("ww", Date())-1) AND DatePart("yyyy", [15th_St_Panel_Plant_FPY.ProductionDate]) = (DatePart("yyyy", Date()));


However, when I compare the query return to manually calculated data from Last Week, the numbers do not match.

The ProductionDate is in the format mm/dd/yyyy. The first day of the week is intended to be Sunday.

Is there something in my Syntax, or am I just wrong in trying to do it this way?
0
Rex
Asked:
Rex
  • 2
  • 2
1 Solution
 
Dale FyeCommented:
try:

WHERE [ProductionDate] >= DateAdd("d", -weekday(Date())-6, Date())
AND [ProductionDate] < DateAdd("d", -Weekday(Date()) + 1, date())
0
 
RexQuality LeaderAuthor Commented:
That worked...Thanks!
0
 
Dale FyeCommented:
You may need to tweak the values of -6 and +1 to get the precise definition of "last week" that you are looking for.

Glad to help.
0
 
RexQuality LeaderAuthor Commented:
Thanks
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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