Solved

Last Weeks in  a query

Posted on 2014-01-20
4
331 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 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