Pulling last week's data

[Bunky]

I have a database that I am extracting data from once a week (usually Monday
morning). This database has multiple records for everyone per day. I would
like to pull all the data for the prior week and not have the user have to
enter any dates (begin - end). So I want to take the current week and somehow
get all the entries from the prior week when the data is stored by date. Is
this possible with queries?

Thanks,

 

[Jerry Whittle]

Something like this should work in the criteria for a date field in a query:

Between DateAdd("ww",-1,Date -DatePart("w",Date,3)) AND DateAdd("ww",-1,Date
-DatePart("w",Date,3)) +6

 

[Jack Cannon]

This is not intended to be a complete answer but you
can try setting the filter to something like:
DatePart("ww", Me![MyRecordDate]) = DatePart("ww", Date()) - 1

Of course you will have to insert the code to get the current year only
and also get the last week of prior year when you are in Week 1 of a new year.

Jack Cannon

 

[Bunky]

Jerry,

Thank you for the solution and it does work fabulously. I had to do so hoop
jumping to use it because the 3rd party database that we are linking to for
the information has the date field name as 'DATE'. Obviously, they do not
want people to get into the data. However, If I bring their Date field in
and use and alias on one query then refer to the alias in the 2nd query, I
get the data I need. It just takes longer to run because the database
contains millions of rows. So in the first query, I told it to just bring in
2009 data to try and speed things up. If you have any thoughts on how to get
around having to use the 2nd query with you coding, please let me know. I'm
at a loss but it does work; just runs a rather long time.

Thanks again!

 

[Douglas J. Steele]

Try using

Between DateAdd("ww",-1,[Date] -DatePart("w",[Date],3)) AND
DateAdd("ww",-1,[Date] -DatePart("w",[Date],3)) +6