MS Access - query date range by day of week

I need to automate a query to produce results from a date range and can't get my head around the format of the criteria needed.

The query must select records from the PREVIOUS Thursday to LAST Friday. IE, today is Thursday 31st March, so today my query should return all results from Thursday 17th March through till Friday 25th March.

If it was Monday 21st March today, it should report on 10th to 18th March

How on earth can I do this?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Where [DateField] >= dateserial(year(date()), Month(date()), day(date()) - weekday(date(), vbSaturday)-8)
AND [DateField] < dateserial(year(date()), Month(date()), day(date()) - weekday(date(), vbSaturday))

RossAuthor Commented:
fyed - thats gives me an error unfortunately.

The field in question is named "Closed". Thus, I have:

Where [Closed] >= dateserial(year(date()), Month(date()), day(date()) - weekday(date(), vbSaturday)-8) AND [Closed] < dateserial(year(date()), Month(date()), day(date()) - weekday(date(), vbSaturday))

It seems not to like vbSaturday. Must this be done via VB? I was attempting to do this in query design (or SQL view within that).

Dale FyeOwner, Developing Solutions LLCCommented:
Sorry, didn't catch that, you cannot use the vbSaturday constant in a query.

Replace vbSaturday with the number 7 and give it another try.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Ross:  What would you expect if today was 1 April and 2 April (Fri and Sat)
RossAuthor Commented:
fyed - I think thats got it - just need to test various dates now.

GRayL - let me clarify.

We run data reports on work carried out for the previous week and send this report out to managers on a Monday morning. However, the results of the work carried out on the Friday just gone aren't in yet, so we run the report (on the Monday) for the previous two weeks, minus the last fridays data.

Does that make sense? Although I gave a few different days in my original post as examples, in real terms, that would never happen as the report will always be run on a Monday.
Last Saturday is always give by Date()-Weekday(Date())
Last Fri is: Date()-WeekDay(Date())-1
The Thu a week before is Date()-WeekDay(Date())-9

Therefore the query becomes:

SELECT * from myTable where fldDate BETWEEN Date()-WeekDay(Date())-9 AND Date()-WeekDay(Date())-1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
This will work for any day falling on a Mon, Tue, Wed, or Thu.
RossAuthor Commented:
perfect - thank you :)
Thanks, glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.