Convert SQL Code to Use in Access Query for Dates

L

Leanne

I would like to use the following code to run on any day of current week and
return data for the previous week, Sun-Sat.
I found the SQL code but it doesn't work in Access. Is there any way to
tweak it so it will work and what would need to be changed?
where
-- Greater than or equal Sunday of last week
saledate >= dateadd(dd,((datediff(dd,-53684,getdate())-7)/7)*7,-53684) and
-- Less than Sunday of this week
saledate < dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)

Thanks for any assistance offered.
LMR
 
D

david

Leanne said:
I would like to use the following code to run on any day of current week
and
return data for the previous week, Sun-Sat.
I found the SQL code but it doesn't work in Access. Is there any way to
tweak it so it will work and what would need to be changed?
where
-- Greater than or equal Sunday of last week
saledate >= dateadd(dd,((datediff(dd,-53684,getdate())-7)/7)*7,-53684)
and
-- Less than Sunday of this week
saledate < dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684)

Thanks for any assistance offered.
LMR

Here is an alternative:

Previous Saturday:
date()-weekday(date())

Sunday before that:
date()-weekday(date()) - 6

saledate >= date()-weekday(date()) - 6
and
saledate <= date()-weekday(date())

Note, this form always gives you last week, not this week. If you run it on
a Saturday, it gives you the previous Saturday.
 

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