Solved

Last Weeks in  a query

Posted on 2014-01-20
4
330 Views
Last Modified: 2014-01-20
I am using  a WHERE clause that properly pulls up the current week in a query. I would like a separate query to pull up LAST WEEK'S data. How can I change this code to be last week?

WHERE Format(Nz([qryStatsforDailySalesReport].[DateField],#1/1/1950#),"wwyy") = Format(Now(),"wwyy")
0
Comment
Question by:cansevin
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39794168
Try this:
WHERE Format(Nz([qryStatsforDailySalesReport].[DateField],#1/1/1950#),"wwyy") = Format(Date()-7,"wwyy")

Open in new window

0
 
LVL 11

Expert Comment

by:Guru Ji
ID: 39794169
You can just say

WHERE Format(Nz([qryStatsforDailySalesReport].[DateField],#1/1/1950#),"wwyy") = Format(Now(),"wwyy") -7

It should work for last week
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39794170
This will knock off 7 days:
WHERE Format(Nz([qryStatsforDailySalesReport].[DateField],#1/1/1950#),"wwyy") = Format(Datediff("dd",-7,Now()),"wwyy") 

Open in new window

0
 
LVL 11

Expert Comment

by:Guru Ji
ID: 39794171
if you have date fields in your database then you can also do something like this below

YourDateField Between (Date() -7) AND Date()

Just to update previous comment you can apply this if above doesn't work


WHERE Format(Nz([qryStatsforDailySalesReport].[DateField],#1/1/1950#),"wwyy") = Format(Now()-7,"wwyy")
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

896 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now