Question Query to show only records for upto the last seven days starting from the Sunday

[Steve_T]

Hello,
I have a table of records which i need to filter via a query to show all data for up to 7 days from the day the query is ran. This in its self is simple using a Date()-7 function but i only want to show records that start from the Sunday within the date parameters.

For example if i ran this query Tuesday 13:00 hrs it would only show records up to last Sunday 0:00 hrs and not before. But if i ran the same query on the Saturday 23:00 it would show all data all the way back to last Sunday 0:00.


Any Help would be greatfuly appreciated.
Thanks

 

[raskew]

Set your criteria as:

>= date() - weekday(date()) + 1

Added:

If you ran it today (Sun 7/6/08), it'd return only records from today.
If, however, you had run it yesterday, the above would equate to:
x = #7/5/08#
? x - weekday(x)+1
6/29/2008 (last Sunday)

HTH - Bob

 

[Steve_T]

Thanks for that it does the trick, just out of interest what do you change to get the report to start from another day instead of the Sunday is it
+1 Sunday
+2 Monday
etc etc????

 

[Steve_T]

I was looking for a way to run this report when ever i wanted without changing any fields. so to show records upto the most recent Sunday.
Will the >= date() - weekday(date()) + 1 criteria you gave me do this?

 

[raskew]

...what do you change to get the report to start from another day instead of the Sunday

You have it correct, e.g. +1 for Sunday, +2 for Monday, etc.

so to show records upto the most recent Sunday.

Showing records up to the most recent Sunday is a different can of worms.

Is that really what you want? If you were to use <=date()-weekday(date())+1,

note the <=, would give you every record in your table with a date

equal or less than the most recent Sunday. Probably

not what you want. Normally you'd want to specify a from date and to date.


Please provide a little more info.

Bob

 

[Steve_T]

Hi sorry for lack of information.
What i am trying to do is create a query where no matter what day it is it will run a show all records upto a max of seven days but not look any further then the most previous Sunday
I.E.

1. If in run it today on Sunday it will only show records for today.
2. If run the report next Saturday (12/07/08) it will show all records for that day,Friday,Thursday,Wednesday,Tuesday,Monday and Sunday only.

Hope this helps

 

[raskew]

Steve -

The criteria provided in Post #2 does just that.

Say you ran the report this coming Saturday, 12-Jul-08 and set the date
criteria as: >= date() - weekday(date() + 1

date() = 7/12/08
weekday(date()) = 7 (Saturday)

so: date() - weekday(date()) + 1 equates to
7/12/08/ -7 = 7/05/06 + 1 = 7/6/08 (i.e. today, Sunday)

HTH - Bob

 

[Steve_T]

Thanks for your help today, you have saved me some sleepless nights