Date Restrictor for last week's sales

[Guest]

Hi,

I have been using a date restrictor in Access and it has worked fine for
years. It isolates transactions for last week and is done on a rolling
basis. I run this report every Saturday. We moved our Access tables to MS
SQL Server but the queries, forms and reports are still in Access. Every
time I run this report now I get no results. I can plug in actual dates and
gets results (ie Date between 9/10/07 and 9/17/07). Here is the date
restrictor:

Between DateValue(Now())-Weekday(DateValue(Now()),2) And
DateValue(Now())-Weekday(DateValue(Now()),2)-7

 

[Wolfgang Kais]

Hello Chuck.

I have been using a date restrictor in Access and it has worked
fine for years. It isolates transactions for last week and is done
on a rolling basis. I run this report every Saturday. We moved our
Access tables to MS SQL Server but the queries, forms and reports
are still in Access. Every time I run this report now I get no
results. I can plug in actual dates and gets results (ie Date
between 9/10/07 and 9/17/07). Here is the date restrictor:

Between DateValue(Now())-Weekday(DateValue(Now()),2) And
DateValue(Now())-Weekday(DateValue(Now()),2)-7

That meanes that the larger date comes first.
I guess that the date field has an index and that Access therefore
lets SQL Server do the filter job. Unfortunately SQL Server doesn't
find any record when looking for a date between 9/17/07 and 9/10/07.
Try to write the restrictor the other way around:

Between Date()-Weekday(Date(),2)-7 And Date()-Weekday(Date(),2)