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?

Cheers!
LVL 2
RossAsked:
Who is Participating?
 
GRayLCommented:
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
0
 
Dale FyeCommented:
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))

0
 
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).

Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
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.
0
 
GRayLCommented:
Ross:  What would you expect if today was 1 April and 2 April (Fri and Sat)
0
 
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.
0
 
GRayLCommented:
This will work for any day falling on a Mon, Tue, Wed, or Thu.
0
 
RossAuthor Commented:
perfect - thank you :)
0
 
GRayLCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.