Previous Week -Sun thru Sat 1

[jw5107]

I am working with some criteria below:
Between DateAdd("d",-9,Date()) And DateAdd("d",-1,Date())

I am trying to get the previous weeks records from Sun thru Sat with out typing in date ranges and stuff. I will be running this every Tuesday. Just need the previous week Sun thru Sat....

Can anybody help me out here. I think I am headed in the right direction, just need some help!!

Thanks in advance!!
jw5107

 

[SkipVought]

Hi,

Try this...
[tt]
StartOfWeek =INT((NOW-2)/7)*7+1
EndOfWeek =StartOfWeek + 6
...
Between StartOfWeek And EndOfWeek
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[dhookom]

I would add a calculated column to the query
YrWk: Format([Date Field],"yyyyww")
and set the criteria to
Format(Date() - 7,"yyyyww")

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

Duane, I think there may be a flaw in using your technique. As in running on Tuesday, Jan. 6, 2004. The equation will provided for a week of 200353 but this would not include the dates of 1/1/2004 thru 1/3/2004 even though they are legitimately part of that week. You see when the week rolls over the beginning of a year this would not select all of the dates necessary.

I think the following should work in this situation. I have not hard coded this to a particular day of the week because maybe the run won't take place on Tuesday for whatever reason.

StartOfWeek = (Date() - 7) - (DatePart("w", Date()) - 1)
EndOfWeek = StartOfWeek + 6

Criteria ...
[red]Between StartOfWeek And EndOfWeek [/red]


Post back if you have any questions.





[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[dhookom]

Good catch Bob.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jw5107]

Scriverb,

I have put your code in a module like this:

Function StartOfWeek(vdate As Date)
StartOfWeek = (DATE - 7) - (DatePart("w", DATE) - 1)
End Function

Function EndOfWeek(vdate As Date)
EndOfWeek = StartOfWeek + 6
End Function

The for the criteria in the query - access enters the quotes around StartOfWeek and EndOfWeek like:
Between "StartOfWeek" And "EndOfWeek"

I then get a "data type mismatch in criteria expression" error.

Can ya help me out....

...again!!! May need to go "4th grade level" for me!!!

Thanks!!!
jw5107

 

[scriverb]

Use the following:

Function StartOfWeek() As Date
StartOfWeek = (DATE - 7) - (DatePart("w", DATE) - 1)
End Function

Function EndOfWeek() As Date
EndOfWeek = StartOfWeek() + 6
End Function

Between StartOfWeek() And EndOfWeek()

There is no need to pass a parameter to the functions as we are using the system date to determine these two dates. Even though you are running this on a Tuesday normally you may need to run it on other days of the week and get the same results.

Post back if you have any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jw5107]

Scriverb,

Awesome!! Thank you very much. No matter what day I run the query it will always give me the previous week Sun - Sat. I like that you went "outside the box" on that one!!!

Thanks again!!!
jw5107

 

[scriverb]

jw: Glad that you liked the technique. Thanks for the Star as it is much appreciated.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]