Query Data Between Weeks

S

Scott

I have a date field called "transactiondate". How do I query all the data
for the previous two full weeks? How do I query the data for the previous 52
full weeks? Not inclusive of the current week.
 
J

Jeff Boyce

Scott

"How" depends on "what". What definitions are you using for "previous two
full weeks". Are you using today as the starting day for the weeks, or a
"Sunday", or ...?

Are you looking for each week separately, or both together?

For the "previous 52...", define "full week", and indicate whether it's the
total for that period or the amount for each week during that period.

What are we supposed to infer from "not inclusive of the current week" (and
define "current week")?

More info, please!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
J

John Spencer

Try the following criteria against TransactionDate

Previous two full weeks (Sunday to Saturday)

Between DateAdd("d",-Weekday(Date()),Date())-13 AND
DateAdd("d",-Weekday(Date()),Date())


Previous 52 weeks

Between DateAdd("w",-52,DateAdd("d",-Weekday(Date()),Date()))+ 1 AND
DateAdd("d",-Weekday(Date()),Date())

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
S

S.Clark

Previous 14 days:
Select * from tablename where transactiondate between date()-14 and date()

Previous 52 weeks, not including last week.
Select * from tablename where transactiondate between date()-365 and date()-7
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top