Convert SQL Code to Use in Access Query for Dates

[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

 

[Roger Carlson]

Try these:
saledate >= dateadd("d",((datediff("d",-53684,date())-7)/7)*7,-53684)
saledate < dateadd("d",(datediff("d",-53684,date())/7)*7,-53684)

I'm only speaking for the sytax to use in Access. I haven't evaluated the
actual function.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[david]

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.