Date Problem - How to identify working days only

BlackFireNova

I need to write a report in which one part shows a count of how many
total records fall within the working days (Monday - Friday) inside of a
(prompted) given date range, in a particular geographical region.

I have written a query which prompts the user for the start and end
dates. It also filters for entries which pertain to the particular
geographical region.

I'm not sure where to go from here.

How do I filter the results to show only the records within the prompted
date range which fall within the Work Week (ie: Monday - Friday)? (
ie: I need to somehow filter out Saturday and Sunday days/dates within
the prompted date range)

Once I have the query filtered to show only records which fall within
the required date range, region, and only within the (Mon-Fri) Work
Week, I then need a simple count of how many records there are. How do
I achieve this result?

Can anyone help me with suggested coding, and structure in the query
grid so as to accomplish this result?

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #1

Wayne Morgan

You should be able to use the Weekday function to eliminate records that occur on certain
days of the week. You may need to adjust for the first day of the week in your locality.

Weekday(#10/23/2003#) = 5 for Thursday, to eliminate weekends you would not want
Weekday([DateField]) to be 1 or 7. You would set this to be a calculated field in the
query.

In the Query design grid
Field: NoWeekends:Weekday([TableName].[DateField])
Criteria: <>1 And <>7

You could uncheck the Show box unless you need the numbers 2-6 (for Mon - Fri) in the
output of the query for something else. It will filter on this even if the value doesn't
show in the output.

Question, if you are only working on Mon through Fri, how did records get in on Sat and
Sun? If there aren't any, you may not need to filter them out. If they are there, then
weren't Sat and Sun work days also?

The syntax for the Weekday function is Weekday(date, [firstdayofweek])

--
Wayne Morgan
Microsoft Access MVP
"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:3f*********************@news.frii.net...

I need to write a report in which one part shows a count of how many
total records fall within the working days (Monday - Friday) inside of a
(prompted) given date range, in a particular geographical region.

I have written a query which prompts the user for the start and end
dates. It also filters for entries which pertain to the particular
geographical region.

I'm not sure where to go from here.

How do I filter the results to show only the records within the prompted
date range which fall within the Work Week (ie: Monday - Friday)? (
ie: I need to somehow filter out Saturday and Sunday days/dates within
the prompted date range)

Once I have the query filtered to show only records which fall within
the required date range, region, and only within the (Mon-Fri) Work
Week, I then need a simple count of how many records there are. How do
I achieve this result?

Can anyone help me with suggested coding, and structure in the query
grid so as to accomplish this result?

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #2

Wayne Morgan

For the second part, this example should work to count them. Adjust for your date limits.

SELECT Count(1) AS CountMe
FROM Table2
WHERE (((Table2.Field2) Between #1/1/2003# And Date()) AND
((Weekday([Table2].[Field2]))<>1 And (Weekday([Table2].[Field2]))<>7));
--
Wayne Morgan
Microsoft Access MVP

Nov 12 '05 #3

BlackFireNova

Wayne:

I should have been a bit more specific.

In the date range, I need to identify the number of records which fall
in a given range (shown below). The range must be Work Days counted,
not including weekends.

Referrals not yet formally assessed (for the past month, or within the
date range specified - Usually 1 month)
5 or less(Working Days)
5-10 (Working Days)
10-21 (Working Days)
21 (Working Days) or older.

I have to somehow set up a series of calculations which counts from the
first date in the prompt, to the next division (5 or less), then
discounts weekends. Then same from 5-10 days, discounting weekend days,
etc., for the range.

BFN

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4

Wayne Morgan

The query in my second reply will do this. You may need to use a form to have the user
input a date range, then click a button to start counting. In the code behind the button,
break the date range up into however many date periods you want within the entered range
and use them in the query to return the results.

SELECT Count(1) AS CountMe
FROM Table2
WHERE (((Table2.Field2) Between #1/1/2003# And Date()) AND
((Weekday([Table2].[Field2]))<>1 And (Weekday([Table2].[Field2]))<>7));

--
Wayne Morgan
Microsoft Access MVP
"BlackFireNova" <BF*****************@myrealbox.com> wrote in message
news:3f*********************@news.frii.net...

Wayne:

I should have been a bit more specific.

In the date range, I need to identify the number of records which fall
in a given range (shown below). The range must be Work Days counted,
not including weekends.

Referrals not yet formally assessed (for the past month, or within the
date range specified - Usually 1 month)
5 or less(Working Days)
5-10 (Working Days)
10-21 (Working Days)
21 (Working Days) or older.

I have to somehow set up a series of calculations which counts from the
first date in the prompt, to the next division (5 or less), then
discounts weekends. Then same from 5-10 days, discounting weekend days,
etc., for the range.

BFN

BlackFireNova

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #5

Bob Quintal

BlackFireNova <BF*****************@myrealbox.com> wrote in
news:3f*********************@news.frii.net:

Wayne:

I should have been a bit more specific.

In the date range, I need to identify the number of records
which fall in a given range (shown below). The range must be
Work Days counted, not including weekends.

Referrals not yet formally assessed (for the past month, or
within the date range specified - Usually 1 month)
5 or less(Working Days)
5-10 (Working Days)
10-21 (Working Days)
21 (Working Days) or older.

You may want to use a calendar table. This version works back from
a given Finish date till the number of days you input has been
input.

You first need to create table calendar with two fields, bookeddate
and daynumber.

'------------------------
Public Sub setCalendar()
Dim dDate As Date
Dim strDays As String
Dim i As Integer
Dim j As Integer
Dim Days As Integer

dDate = Date
strDays = InputBox("Enter the number of days")
If strDays = "" Then GoTo exit_setDateRange
Days = CInt(strDays)
DoCmd.RunSQL "DELETE * from calendar"
DoCmd.RunSQL "Insert into calendar " & _
"(BookedDate,daynumber) " & _
"VALUES (#" & dDate & "# ," & j & ");"

For i = 1 To Days
If WeekDay(dDate - i, 1) <> 1 & _
And WeekDay(dDate - i, 1) <> 7 Then
j = j + 1
End If
DoCmd.RunSQL "Insert into calendar " & _
"(BookedDate,daynumber) " & _
"VALUES (#" & (dDate - i) & "# ," & j & ");"
Next i
exit_setDateRange:
Exit Sub
End Sub
'----------------------

This will give you a list of dates and the number of workdays
between then and today.

BookedDate daynumber
Friday, October 10, 2003 10
Saturday, October 11, 2003 9
Sunday, October 12, 2003 9
Monday, October 13, 2003 9
Tuesday, October 14, 2003 8
Wednesday, October 15, 2003 7
Thursday, October 16, 2003 6
Friday, October 17, 2003 5
Saturday, October 18, 2003 4
Sunday, October 19, 2003 4
Monday, October 20, 2003 4
Tuesday, October 21, 2003 3
Wednesday, October 22, 2003 2
Thursday, October 23, 2003 1
Friday, October 24, 2003 0
I have to somehow set up a series of calculations which counts
from the first date in the prompt, to the next division (5 or
less), then discounts weekends. Then same from 5-10 days,
discounting weekend days, etc., for the range.

BFN

Nov 12 '05 #6