473,735 Members | 3,971 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Date Problem - How to identify working days only

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
5 14894
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:Week day([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
"BlackFireN ova" <BF************ *****@myrealbox .com> wrote in message
news:3f******** *************@n ews.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
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.Field 2) 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
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
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.Field 2) Between #1/1/2003# And Date()) AND
((Weekday([Table2].[Field2]))<>1 And (Weekday([Table2].[Field2]))<>7));

--
Wayne Morgan
Microsoft Access MVP
"BlackFireN ova" <BF************ *****@myrealbox .com> wrote in message
news:3f******** *************@n ews.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
BlackFireNova <BF************ *****@myrealbox .com> wrote in
news:3f******** *************@n ews.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_setDateRan ge
Days = CInt(strDays)
DoCmd.RunSQL "DELETE * from calendar"
DoCmd.RunSQL "Insert into calendar " & _
"(BookedDate,da ynumber) " & _
"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,da ynumber) " & _
"VALUES (#" & (dDate - i) & "# ," & j & ");"
Next i
exit_setDateRan ge:
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
3251
by: Simon Dean | last post by:
Probably being a little thick here, but when you subtract one date away from another, how do you convert the resultant value into a number of days... I guess I could easily / 60 / 60 / 24... but that seems barbaric... Anything neater? Cheers Simon Ps, Im also just trying to work out how to calculate the number of mondays and tuesdays etc between two dates... Just thought I'd run this
3
7724
by: Jason Huang | last post by:
Hi, In our C# Windows Form application, we are using the SQL Server 2000 as the database server. The Database table MyTable has a field RegistrationDate which represents the Date a client comes to our company to deliver his product. We have some Working days for processing his product, like 20, 30, 40 days. My question is how do I handle the working days problem, e.g., a client comes at the Days February 15 where we have 40 Working...
0
1532
by: kumarkanth | last post by:
My question is how do I manipulate the procedure to calculate cut-off date with in working days and it should skip weekends and holidays? I have holiday table to determine holidays Table Name : HOLIDAYMSTR HOLIDAYMSTR_ID RAW(16) PREV_UPDATED_BY RAW(16) Y LAST_UPDATED_BY RAW(16) HOLIDAY_DATE DATE ...
29
3222
by: Santiagoa | last post by:
If I set up a task table with an Date_assigned and a number of days to complete the task I calculate the end_date field by using the code below I found in this forum How ever when I enter the Date_assigned and update the record, nothing happens until I manually enter a value in the DaysToComplete field. I want to keep DaysToComplete Constant (5 Days) so I tried to set the attribute in the table with 5 as the default but this does not work....
2
1559
by: Maninder Karir | last post by:
Hi, Im trying to work out how to return a date based on addition of working days only. On the userform the user enters the date using dtpicker. They then have the option to add 5, 10 or 15 days. I need this to then return the date, adding only the working days, in a new Text box. Any deas?
1
2201
by: almurph | last post by:
Hi, I have a trickly little problem that I hope you can help me with. I need to add *working* days to a DateTime object of the format: yyyy/MM/ dd For example: say I have a timestamp of the following date: 2008/10/24 <--tomorrow
1
1200
by: KasSamrari | last post by:
Hello, I am working on an access dB where the release date is only populated once the products have been released otherwise remain blank. I can calculate the working days if the MfgDate and FFReleaseDate are both populated but get an #error if the FFReleaseDate is blank. I would like the working days to be zero if the release date field is blank. Doing this with vba code (Attached). Please help. Regards, Kas
3
2492
Jerry Maiapu
by: Jerry Maiapu | last post by:
Hi Scott Prince, Since this dean is not interested I need to do the same as Dean is trying to do. Since Dean has not replied would you mind to show me how to do this: You'll have to open this into a recordset programatically and loop through comparing each date with the fixed holiday date to get your day count minus holidays. Then you can run the result through the weekday count function to return a final number of working days. I already...
2
6864
by: Organised Angel | last post by:
Hi everyone I'm doing a project at work and I'm managing most of it fine apart from one section where I need to calculate a due date. The due date is 5 working days after the date entered. I have a query and I've used the Due Date:DateAdd("d","5",) calculation which does work out 5 days but is including weekends - I've tried substituting the "d" days for "w" days but still no luck! Help! I think I need a table of holidays but this is way...
0
9463
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9251
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9200
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4562
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3274
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2740
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.