Query for previous week, Sun thru Sat, from date field

[Leanne]

I run a query for distribution via macro. I could use the 'between dates'
criteria, but is there a better way? I have code 'Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)' that will pull data for the
previous month without using 'between dates'. Can it be modified for previous
week?

 

[John Spencer]

Between DateAdd("d",1-WeekDay(Date())-7,Date()) And
DateAdd("d",1-WeekDay(Date())-1,Date())


For today's date (April 9, 2010 that should return
Between #3/28/2010# and #4/3/2010#

On April 11, 2010 (SUNDAY) that will return
Between #4/4/2010# and #4/10/2010#


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