Solved

Last Weeks in  a query

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

837 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