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

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/...UBED1=ACCESS-L



"Leanne" <(E-Mail Removed)> wrote in message
news:95E272A3-F2A2-44C6-A735-(E-Mail Removed)...
>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

"Leanne" <(E-Mail Removed)> wrote in message
news:95E272A3-F2A2-44C6-A735-(E-Mail Removed)...
>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.