Query for items within a date range for a weekly report

[connolly_tm]

[FONT=&quot]I need to select service requests for the current week -- beginning Sunday and through Saturday. The service requests have a StartDate and a Duration value which is a number between 1 and 35 representing the number of days the service is required. Can't figure out how to use the ServiceRequestDays (=StartDate + Duration) in a query/filter to select the transactions that should be reported for a given work week. Any query/filter suggestions would be wonderful help.[/FONT]

 

[PNGBill]

[FONT=&quot]I need to select service requests for the current week -- beginning Sunday and through Saturday. The service requests have a StartDate and a Duration value which is a number between 1 and 35 representing the number of days the service is required. Can't figure out how to use the ServiceRequestDays (=StartDate + Duration) in a query/filter to select the transactions that should be reported for a given work week. Any query/filter suggestions would be wonderful help.[/FONT]

This sql will return all records where the StartDate plus Duration is Before today plus seven days. ie Was expected to be ready by now or is expected to be ready in the next 7 days.

SELECT tbleDateTest.StartDate, tbleDateTest.Duration, [StartDate]+[Duration] AS FinishDate
FROM tbleDateTest
WHERE ((([StartDate]+[Duration])<Date()+7));

 

[SteveSchapel]

Connolly,

Off the top of my head, I am pretty sure this takes care of all eventualities:

(Date()-Weekday(Date())+1 Between [StartDate] And [StartDate]+[Duration]) Or
(Date()-Weekday(Date())+7 Between [StartDate] And [StartDate]+[Duration]) Or ([StartDate] Between (Date()-Weekday(Date())+1) And (Date()-Weekday(Date())+7))

 

[PNGBill]

This version should give you all records where the Startdate plus duration is due this week.

SELECT tbleDateTest.StartDate, tbleDateTest.Duration, [StartDate]+[Duration] AS FinishDate
FROM tbleDateTest
WHERE ((([StartDate]+[Duration])<Date()+7-Weekday(Date())));

Here we take the Day of the week and subtract this from today's date and then add 7. In my case, I am in Sunday so we are essentially using Today.
Tomorrow it is Monday and Weekday will be 1. Tomorrows Date less 1 plus 7 gives you next week.

 

[SteveSchapel]

Bill,

Won't that include all the ones that started and finished before the start of the current week? And what about the ones that started during the current week, but not yet finished?

 

[PNGBill]

I understood the request was to display any items that should have been finished by now and should be finished this week.

Say an item was supposed to be finished last week but wasn't, then it should be included in future lists and if list was sorted by Date Due then this item would be at the top of the list, where it should be.

 

[PNGBill]

Bill,

Won't that include all the ones that started and finished before the start of the current week? And what about the ones that started during the current week, but not yet finished?

The simple query didn't account for items completed. This would require one additional field to restrict completed items.

DateStarted and Duration, would only give an Estimated Finish Date.
Some Completed Date or Completed Flag would be required to restrict these items rather then an assumption an item was completed - I guess.

 

[SteveSchapel]

Fair enough, Bill. I see we are interpreting the original question quite differently from each other. Maybe Connolly will come back and clarify the requirement, or maybe we will never know.

 

[PNGBill]

Steve,

I just looked at it quickly so I may well be off the mark.

Often the question doesn't give the final output requirement anyway so now the OP has some food for thought.

Actually, I just played with a test database I had to see what I could do, just in case I came up with a similar issue.

Do you mind explaining your expression as I must admit, it confuses me a little and the problem is, I am sure it must work but what does it do?

Quite weekend here so brain not in high gear.

 

[SteveSchapel]

Bill,

No worries. My thinking may be twisted here too. But this is the way I understood the question... Connolly wants his query to return all Service Requests that are current/alive during any part of the current week.

So this is the way I figured it:

1. Date()-Weekday(Date())+1 Between [StartDate] And [StartDate]+[Duration]
This will give you the requests whose StartDates were before the start of the week, and whose end dates were after the beginning of the week, i.e. the requests that were alive at the beginning of the week.

2. Date()-Weekday(Date())+7 Between [StartDate] And [StartDate]+[Duration]
This will give you the requests whose StartDates were before the end of the week, and whose end dates were after the end of the week, i.e. the requests that were alive at the end of the week.

3. So that only leaves any requests whose start dates and end dates were within the week, i.e. started after the beginning of the week, and Duration only a few days, so not alive at the beginning of the week nor the end of the week, but alive during part of the middle of the week. So we add this to the criteria to make sure we pick these up as well:
[StartDate] Between (Date()-Weekday(Date())+1) And (Date()-Weekday(Date())+7)

I didn't test it, but looks ok to me. Does it make sense?

 

[SteveSchapel]

Mind you, having slept on it, I now see that I did make it more complicated than needed. If the Service Request was "alive" at the beginning of the week, or if the Service Request was started during the week, should cover all eventualities. The other factor I included, of whether it was alive at the end of the week, I think is redundant. Therefore, I amend my earlier suggestionn to:

(Date()-Weekday(Date())+1 Between [StartDate] And [StartDate]+[Duration]) Or ([StartDate] Between (Date()-Weekday(Date())+1) And (Date()-Weekday(Date())+7))

 

[connolly_tm]

I am absolutely humbled by the advice and responsiveness to my inquiry. And the answers -- WOW -- no wonder I couldn't figure out how to solve this problem. Mr Steve Schapel -- your restatement of the problem is correct, but I would like to clarify that the service requests can be effective for any day of the week, not only weekdays. Is the Weekday reference in your solutions intended to limit records to weekdays and not any/all days of the week?

I am trying to test your solution and will continue to and report my success as I can.

Thanks to all of you for your very thoughtful and generous assistance.

tmc

 

[connolly_tm]

While I was trying to comprehend and digest Mr Steve Schapel's initial posted solution after which I decided to post an intervening comment, Mr Schapel's "simpler solution" has come across to me.

I'll incorporate that into my analysis also.

Many thanks again.

tmc

 

[SteveSchapel]

TMC,

The Weekday function returns a digit to indicate the day of the week. So it doesn't relate to the concept of working days and weekends. By default, the Weekday value of Sunday is 1 and the Weekday value of Saturday is 7. Which is handy, as you can use it, for example, to find the date of any related day of the week, using any nominated date as the starting point. So, for example, 9th September is a Thursday. We can find the date of the preceding Sunday by:
#9/09/2010# - Weekday(#9/09/2010#) + 1

So I used this idea to evaluate, for any given "current date" (using the Date() function), the dates for the current Sunday-Saturday week.

Hope that makes sense.

 

[connolly_tm]

Mr Schapel

Thank you for that insight and I also thank you very much for the solution that I have tested in many ways and find spot on.

I appreciate the assistance very much.

tmc

 

[connolly_tm]

Mr Schapel

Now I get it.

And thank you for the solution -- I've tested it in all the ways I can and find it 100% on.

Very much appreciate the assistance.

tmc

 

[SteveSchapel]

TMC,

I am happy to help, and thanks for letting us know of your success. Best wishes with the rest of the project.