Solved

Last Weeks in  a query

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

746 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

12 Experts available now in Live!

Get 1:1 Help Now