Returning to the scene of the crime-
The requester hoped for a query, thinking it would be simple and something with which he/she'd had some experience. In fact, a query solution is probably the most difficult way to resolve the problem. Consider this:
The process of determining work days normally involves:
(1) Determine the number of workdays in the start week.
(1) Determine the number of full, 7-day weeks, between the start and end dates, and multiplying that by 5 (number of workdays in a full week)
(1) Determine the number of workdays in the end week.
(1) Total steps 1, 2 & 3.
(1) Determine if we want both start and end dates included in total. For example, if a process starts on Wednesday and concludes on Thursday, is the number of workdays involved 1 or 2? The answer can vary. If we start a process at 0800 and conclude it at quitting time (1630), we've used an entire day so we might say that process involved one day. Conversely, if we start a process at 1600 on day 1 and conclude it at 1615 on day 2, how many days do we record? Another example: start at 0800 on day 1, conclude at 1630 on day 2. Keeping in mind that we're recording only days, not times, the answer on how to handle the situation involves an arbitrary decision on the part of the user. Once determined, we need to add some code to correspond with the desired response.
(1) Once we've resolved this ugliness, design a work-around to deal with the situation where the start and end dates fall within the same week, because steps 1 & 3 won't work in this scenario.
I'd hope that you'd agree that putting all of the above into a query is probably not a job for the inexperienced or faint-of-heart. While it can be accomplished with a query, the resulting query-SQL is going to be 'the mother of all queries'.
Having dealt with this over a period of years, finally figured out that it would be much easier to let the computer do what it does best-looping through a group of variables. In other words, create a variable named n and give it the value of 0, then
Starting with the Start Date:
(1) Is the start date a weekday? If so, add 1 to n.
(1) Add 1 day to the start date
(1) Repeat Step 1.
(1) Continue to do this until the Start Date = End Date.
When it's looped its way through, you only need to deal with the problem discussed in (5), above.
So, what's wrong with this solution? It's code versus query. The inexperienced requester is afraid of code, thus he/she is asking for a solution expressed in a query (the most difficult one to achieve).
I'll be the first to admit that my response was possibly unnecessarily harsh. If so, I apologize (but I doubt that that was the case)!! If you've viewed and responded to Access forums for any length of time (I've been doing it for 4+ years), I hope that you've noted that some requesters will post multiple (sometimes into the 100s) requests for assistance, and invariably use the "…oh poor me, I don't know much about Access and will you please provide a response based on a macro, a query, or whatever." These folks would like you to believe that they are just incapable of learning anything and are entirely confused. And, at the end of every pay-period, they draw a paycheck, for being an Access programmer.
Have you noticed that while they present pitiful requests for assistance, those requests are always business based? When was the last time you saw someone asking for help with his or her stamp-collection? The point being, they are getting paid to program in Access! This is their job, for which they earn money. All of us, me included, occasionally run up against a brick-wall, and need someone to put us back on the right track.
That's what forums are all about. Conversely, they're not about helping people who aren't interested in helping themselves but rather look to leach off of other's expertise. Please tell us why you want to defend these parasites.
Look forward to hearing from you,
Bob
