Module Error Handling Question (1 Viewer)

[DeanFran]

I've been learnig about dates and Access and was surprised to learn that there's no native function determine the number of weekdays between 2 dates as there is in Excel. I googled around and found a couple of functions that do that job, and have been noodling around with using one of the methods I found in various ways. Of course I quickly stumbled upon the fact if there's only one date of the required two, you get an error for a result. I imagine the real life application of something like this would have to work around this likely scenario. I'm just looking for a nudge in the right direction. Here's the function I've been experimenting with.

Public Function MyNetworkDays(startDate As Date, endDate As Date) As Long
    Dim D As Date
    MyNetworkDays = 0
    D = startDate
    While D < endDate
    If Weekday(D) > 1 And Weekday(D) < 7 Then MyNetworkDays = MyNetworkDays + 1
D = D + 1
    Wend
End Function

 

[spaLOGICng]

Good Recommendation. I too have a similar table named [Counter_and_Report_Dates]. It is iterated from Day One and with an additional 20 years of dates. I named the Date column [Report_Date] and is computed from the autonumber ID Column. I then have several other Computed Columns (boolean type) calling functions return the WeekDay Number, return whether the Report Date is a Weekend, is the First Day of Week, is the Last Day of Week, is First Day of Month, and is Last Day of Month. Is very useful for other purposes. For instance, if I intentionally need to duplicate a Record, I will have a support query to iterate the number of records I need to duplicate using a Between Clause on the ID Column.

I also use the Table for Reports, where I need to iterate every day of the time period, even if that Date does not have any Records to Report, using a Left Outer Join.

 

[Auntiejack56]

where are all our mathematicians? Version with no explicit loop ...

Function myNetWorkDays(dteStart As Date, dteEnd As Date) As Long
Dim i As Long, dteSun1 As Date, dteSat1 As Date
Dim nCountAllSaturdays As Long, nCountAllSundays As Long

i = Weekday(dteStart)


If i > 1 Then
    dteSun1 = DateAdd("d", 8 - i, dteStart)
Else
    dteSun1 = dteStart
End If
If i < 7 Then
    dteSat1 = DateAdd("d", 7 - i, dteStart)
Else
    dteSat1 = dteStart
End If

nCountAllSundays = DateDiff("d", dteSun1, dteEnd) \ 7
nCountAllSaturdays = DateDiff("d", dteSat1, dteEnd) \ 7

myNetWorkDays = DateDiff("d", dteStart, dteEnd) - (nCountAllSaturdays + nCountAllSundays) - 1


End Function

 

[Auntiejack56]

Sorry, bug removal, and I should have been clearer that this includes both start and end dates, and if you want holidays then put 'em into a table. If you want to put date ranges for employee holidays into your holiday table (at the moment there is just a StartDate), there will be a chunk more code needed ...

Function myNetWorkDays(dteStart As Date, dteEnd As Date, Optional EmpID) As Long
Dim i As Long, dteSun1 As Date, dteSat1 As Date
Dim nCountAllSaturdays As Long, nCountAllSundays As Long, n As Long

i = Weekday(dteStart)


If i > 1 Then
    dteSun1 = DateAdd("d", 8 - i, dteStart)
Else
    dteSun1 = dteStart
End If
If i < 7 Then
    dteSat1 = DateAdd("d", 7 - i, dteStart)
Else
    dteSat1 = dteStart
End If

nCountAllSundays = 1 + DateDiff("d", dteSun1, dteEnd) \ 7
nCountAllSaturdays = 1 + DateDiff("d", dteSat1, dteEnd) \ 7

myNetWorkDays = DateDiff("d", dteStart, dteEnd) + 1 - (nCountAllSaturdays + nCountAllSundays)

myNetWorkDays = myNetWorkDays - DCount("*", "tblHolidays", "EmpID is null and #" & Format(dteStart, "yyyy/mm/dd") & "# <= StartDate and #" & Format(dteEnd, "yyyy/mm/dd") & "# >= StartDate")


End Function