how to get records from a range of dates relative to current date

I want to run a report daily that gives me the last week's records. I used
to use "Between DateRelToday(-7) and DateRelToday(0)", but that doesn't seem
to work anymore.

Hi Cathy,

Use:

Between DateAdd("d", -6, Date()) And Date()

To get the last seven days, including the current date. Or:

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

To get the last seven days, excluding the current date.

Clifford Bass

"Cathy C." wrote:

I want to run a report daily that gives me the last week's records. I used
to use "Between DateRelToday(-7) and DateRelToday(0)", but that doesn't seem
to work anymore.

I assume that DateRelToday is ( or was) a function in your database, but you
don't really need to call that; you can use the built in DateAdd function.
As the criteria for the relevant date column in a query on which the report
is based put:

Between DateAdd("d",-6,Date) And Date

which will return the last seven days, including today. For the last seven
days prior to today you'd use:

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

One caveat he the use of a Between…And operation does assume that none
of the values in the column have dates with a non-zero time of day. Unless
you've taken specific steps to prevent this in the table design you cannot
absolutely guarantee that this will be the case. Any rows with dates on the
last day of the range with non-zero times of day would not be returned. This
is because a 'date value' is really the point in time at midnight at the
start of the day, so any with a time of day after that will not equate with
the 'date value'. You can cover this possibility by using the following
criterion:

= DateAdd("d",-6,Date) And [YourDateField] DateAdd("d",1,Date)

or for the last seven days prior to today:

= DateAdd("d",-7,Date) And [YourDateField] Date

where YourDateField is the name of the column in question. Note that if you
do use this more 'bulletproof' expression for the criteria, if you save the
query and then open it again in design view you'll find Access will have
moved things around a bit. It will still work the same however.

Ken Sheridan
Stafford, England

"Cathy C." wrote:

I want to run a report daily that gives me the last week's records. I used
to use "Between DateRelToday(-7) and DateRelToday(0)", but that doesn't seem
to work anymore.

"that doesn't seem to work anymore"

Can you be a bit more explicit? Do you get an error message? Do you get the
wrong results? If you get an error message then it probably means that
DateRelToday is a custom VBA function that you have not copied to the current
database.

Also, define last week's records - the last seven days of records or the
records for the prior week (Sunday to Saturday).

Prior 7 days and today
Between DateAdd("d",-7,Date()) and Date()

Prior 7 days not including today
Between DateAdd("d",-7,Date()) and DateAdd("d",-1,Date())

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

Cathy C. wrote:
I want to run a report daily that gives me the last week's records. I used
to use "Between DateRelToday(-7) and DateRelToday(0)", but that doesn't seem
to work anymore.