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

  • Thread starter Thread starter Cathy C.
  • Start date Start date
C

Cathy C.

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 here: 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)
Click to expand...

or for the last seven days prior to today:
= DateAdd("d",-7,Date) And [YourDateField] < Date
Click to expand...

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
 
Back
Top