UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Networkdays Function    
 
   
ramsfan
post May 6 2005, 08:36 PM
Post #1

UtterAccess Enthusiast
Posts: 99



I am trying to create a query to calculate the networkdays, thus, trying to use that function similar to doing it in Excel. I only want week days, no weekends and no holidays. What is my best approach in Access?
Thank you in advance all you experts!
Go to the top of the page
 
+
ScottGem
post May 7 2005, 08:28 AM
Post #2

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



If you had searched UA on NetworkDays you would have found several threads that show a custom function for this. Like mine:
ublic Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer
Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer
intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If WeekDay(dteCurrDate, vbMonday) < 6 Then
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
NetWorkdays = NetWorkdays + 1
End If
End If
Next i
End Function
There is no built in function so you have to create your own. This means creating a Holidays table with the holiday dates (HolDate) you want to exclude.
HTH
Go to the top of the page
 
+
ramsfan
post May 8 2005, 09:49 PM
Post #3

UtterAccess Enthusiast
Posts: 99



Actually, I did search and saw this, however, I wasn't sure how to call the function in the query. I think I use that name as the expression and use the proper field names to match your code. Anyway, I will try it ... again, thank you for taking the time to reply.
t. Louis Rams Fan
Go to the top of the page
 
+
ScottGem
post May 9 2005, 07:52 AM
Post #4

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



I believe most of the threads do also say how to use the function. But you use it like you would ANY function. To use a function in a query you would add a column with an expression like:
orkDays: NetWorkDays([StartDate],[EndDate])
Go to the top of the page
 
+
royttm
post May 26 2005, 03:20 AM
Post #5

New Member
Posts: 14
From: Singapore



Hi Scott,
I've tried using the function in a Query, but It prompt me undefined function "NETWORKDAYS' in expression.
Please advice me where do I made the mistake.
Thanks.
Attached the test db.
Go to the top of the page
 
+
ScottGem
post May 26 2005, 09:11 AM
Post #6

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



That's because you didn't create the function. You have to copy the code into a module. This is a CUSTOM function not a built in one. Also the function requires a Holidays table unless you comment out those lines.
Go to the top of the page
 
+
bakersburg9
post Jun 5 2007, 06:47 PM
Post #7

UtterAccess Ruler
Posts: 4,375
From: Downey, CA



Scott,
That worked out great for me - thanks !
Steve
Go to the top of the page
 
+
skylite
post Nov 11 2008, 07:45 PM
Post #8

New Member
Posts: 15



I have been successfully using the custom function above in conjunction with a Holidays table. The expression I use in my query is
BUCKET: IIf(NetWorkdays([REPORT_DATE],[DUE_DATE]-1)>15,"16+",IIf(NetWorkdays([REPORT_DATE],[DUE_DATE]-1)>10,"11-15",IIf(NetWorkdays([REPORT_DATE],[DUE_DATE]-1)>5,"6-10",IIf(NetWorkdays([REPORT_DATE],[DUE_DATE]-1)>0,"0-5","PAST DUE")))).
For some reason, the query will not run with a report date value of #11/10/2008#. The error message I get is
Run-Time error '6':
Overflow
It works fine with a value of #11/9/2008# or #10/29/2008#, or any other date. There's just something strange about #11/10/2008#. Any ideas? Thanks!
Go to the top of the page
 
+
oninsky
post Jul 26 2009, 01:17 AM
Post #9

New Member
Posts: 1



I try to use this code however when I try to setup some holidays date it didnt exclude it to the count. Any suggestions?
Go to the top of the page
 
+
Vergy39
post Nov 20 2009, 11:16 AM
Post #10

UtterAccess Member
Posts: 36



Hi Scott, I used your function and it worked great. I was wondering if it could be tweaked a bit to include time. The start date and end date in my DB include the time it was received and time it was closed. Example: Date Received = 10/14/2009 9:07 am Date Closed = 10/27/2009 11:30 am. This should calculate to 9 days, 2 hours and 23 minutes. Can this be done. Any assistance you provide is greatly appreciated.
Thanks
David V.
Go to the top of the page
 
+
ScottGem
post Nov 20 2009, 12:17 PM
Post #11

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



Yep, can do. The key would be to change the intGrossDays line to:
ntGrossDays = DateDiff("n", dteStart, dteEnd)
This will calculate the difference in minutes. From there you can calculate the number of days by dividing by 1440 (the number of minutes in a day. then use the Mod function to get the remnder and divide by 60. Finally, again use the Mode function to get the remaining minutes.
You can subtract out the holidays from the number of total days.
Go to the top of the page
 
+
Vergy39
post Nov 20 2009, 12:36 PM
Post #12

UtterAccess Member
Posts: 36



Thanks for the quick reply. I was able to change the "d" to an "n" in the line you mentioned above. However, I did not understand the rest of your note. Also, for some reason, when I run this code, it returns the records multiple times. For example, an associate has 2 issues. It pulls the same 2 issues several times. Not sure why this is happening. I pasted the code exactly the way you had it listed, and only changed the part on how you call it up by using the field names instead of "start date and end date". Again, your assistance is greatly appreciated.
Attached File(s)
Attached File  Letter Log New.zip ( 51.85K ) Number of downloads: 25
 
Go to the top of the page
 
+
ScottGem
post Nov 20 2009, 01:08 PM
Post #13

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



The original code just counted days. So using your example intGrossDays would equal: 13103
9 (days) * 1440 (minutes) + 2 (hrs) * 60 (minutes) +23 (minutes)
o you add variables for Days (intDays), Hours (intHours) and Minutes (intMinutes).
You then add code like this:
intDays = intGrossDays/1440
The result would be 9.0993 (13103/1440), truncated to 9.
intHrs = (intGrossDays MOD intDays)/60
The MOD operator returns the remainder of a division. So 13103 Mod 9 = 143, then divide that by 60 returns 2.
Finally, you can then use:
intMinutes = intGrossDays) - (intDays * 1440) - (intHours*60)
This will result in 23.
To display the answer you need to display it as a string So you need to change the type returns to String from Integer and add this line:
NetWorkDays=intDays & " Days " & intHrs & " Hours " & intMinutes & " Minutes"
Not sure what you mean by getting the answer twice. Where are you putting the function?
Go to the top of the page
 
+
Vergy39
post Nov 20 2009, 02:13 PM
Post #14

UtterAccess Member
Posts: 36



I tried what you said, but I guess I am not doing something right. I am a novice at this, so I apologize. Here is what I have.
ub Module1()
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) NetWorkDays=intDays & " Days " & intHrs & " Hours " & intMinutes & " Minutes")As String
Dim intGrossDays As String
Dim dteCurrDate As Date
Dim i As Integer
intGrossDays = DateDiff("n", dteStart, dteEnd)
intDays = intGrossDays/1440
intHrs = (intGrossDays MOD intDays)/60
intMinutes = intGrossDays) - (intDays * 1440) - (intHours*60)
NetWorkdays = 0
For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If Weekday(dteCurrDate, vbMonday) < 6 Then
If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
NetWorkdays = NetWorkdays + 1
End If
End If
Next i
End Function
Opasted this as a module. Then I pull a query in design view and add the fields I want to include in the query. After that, I enter "WorkDays: NetWorkDays([DateAssigned],[1stDraftDate]) into the field cell of the query.
Hope ths helps you help me.
Thanks
David V.
Go to the top of the page
 
+
ScottGem
post Nov 20 2009, 02:25 PM
Post #15

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



CODE
Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As String
im intGrossDiff As Integer, intDays As integer, intHrs As Integer, intMin As Integer
Dim intNetDays As Integer
Dim dteCurrDate As Date
Dim i As Integer
intGrossDiff = DateDiff("n", dteStart, dteEnd)
intDays = intGrossDays/1440
intHrs = (intGrossDays MOD intDays)/60
intMinutes = intGrossDays) - (intDays * 1440) - (intHours*60)
intNetDays = 0
For i = 0 To intDays
     dteCurrDate = dteStart + i
     If Weekday(dteCurrDate, vbMonday) < 6 Then
          If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
               intNetDays = intNetDays + 1
          End If
     End If
Next i
NetWorkDays=intNetDays & " Days " & intHrs & " Hours " & intMin & " Minutes")
End Function

Try it that way.
Ochanged some of the names to be more accurate and so I wouldn't have to type as much wink.gif
Go to the top of the page
 
+
Vergy39
post Nov 20 2009, 03:09 PM
Post #16

UtterAccess Member
Posts: 36



I tried this, and got a syntax error at the line intHrs = intGrossDays Mod intDays)/60. Also, do I need to change the dteStart and dteEnd to what is listed on my fields?
Thanks for your help.
David V.
Go to the top of the page
 
+
Vergy39
post Nov 20 2009, 03:10 PM
Post #17

UtterAccess Member
Posts: 36



OH, I guess I should also tell you that I am in version 2000. Sorry about that.
Thanks
David V.
Go to the top of the page
 
+
ScottGem
post Nov 20 2009, 03:19 PM
Post #18

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



OK, now here's the thing. I mentioned I changed some of the names. So you don't see a Dim statement for intGrossDays anymore. So that error should be easy to spot and fix.
Just change intGrossDays to intGrossDiff.
Go to the top of the page
 
+
Vergy39
post Nov 20 2009, 04:23 PM
Post #19

UtterAccess Member
Posts: 36



OK, Cool, getting somewhere now. I got it to run with your help. However, it is returning the days, but the hours are all zero.
AssignedTo CustomerLastName DateAssigned Date1stDraft WorkDays
Vergara Customer 8/14/2009 10:00:00 AM 8/20/2009 3:15:00 PM 5 Days 0 Hours 0 Minutes
You've been great
thanks
Go to the top of the page
 
+
ScottGem
post Nov 20 2009, 04:33 PM
Post #20

UtterAccess VIP / UA Clown
Posts: 27,723
From: LI, NY



You need to step through the code to make sure the values are calculating correctly.
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 30th October 2014 - 06:57 PM

Tag cloud: