Solved

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

Posted on 2013-05-22
4
457 Views
Last Modified: 2013-05-22
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
Comment
Question by:Rex85
  • 2
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39187990
try:

WHERE [ProductionDate] >= DateAdd("d", -weekday(Date())-6, Date())
AND [ProductionDate] < DateAdd("d", -Weekday(Date()) + 1, date())
0
 

Author Closing Comment

by:Rex85
ID: 39188012
That worked...Thanks!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39188033
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
 

Author Comment

by:Rex85
ID: 39188453
Thanks
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question