Determine 3 day return excluding weekends

[dtoney]

My main table is generated via a pass through query and contains a root cause
field with a code value "TBD". These TBDs should be populated with a valid
root cause within 3-days. Our mainframe database stores the date the table
was updated. However, I do not know how to go about designing a process to
determine if the 3-day period has been met.... while excluding weekends.

Would it be best to create a new table with the updated fields? Should I
store the incident number then do a passthrough query to check to see if
these TBDs have been updated? What would be the best way to design this
process in which I can not only determine if the 3-day period has been met
and update my main data table in my Access database to reflect the new
values? My biggest delimma is the exclusion of the weekends. Any advice you
have would be greatly appreciated!

 

[NG]

Hi,

first of all: do only week-ends have to be considered, or do you have to
skip the official holidays (like christmas) too?
If only week-ends, you can use the Access function weekday() to check for
Sundays and Saturdays, if you have to check for holidays too, I would also
use an extra table where, for each year, you note the holidays.

 

[Klatuu]

Not enoug data to answer your entire question, but creating extra tables is
probably not necessary. Here is a function that will determine the number of
days excluding weekends and holidays:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list",
"[holidate] between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--------------------------------

If you want to include holidays in the count, just comment out this line:

CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list",
"[holidate] between #" _
& dtmStart & "# And #" & dtmEnd & "#")

 

[dtoney]

Thanks a bunch! I'll let you know the outcome.

Not enoug data to answer your entire question, but creating extra tables is
probably not necessary. Here is a function that will determine the number of
days excluding weekends and holidays:

'---------------------------------------------------------------------------------------
' Procedure : CalcWorkDays
' DateTime : 5/8/2006 16:34
' Author : Dave Hargis
' Purpose : Counts the number of days between two dates excluding Saturdays,
' : Sundays, and any days in the Holidays table
'---------------------------------------------------------------------------------------
'
Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer

On Error GoTo CalcWorkDays_Error

'Calculates the number of days between the dates
'Add one so all days are included
CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _
(DateDiff("ww", dtmStart, dtmEnd, 7) + _
DateDiff("ww", dtmStart, dtmEnd, 1)) + 1
'Subtract the Holidays
CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list",
"[holidate] between #" _
& dtmStart & "# And #" & dtmEnd & "#")

CalcWorkDays_Exit:

On Error Resume Next
Exit Function

CalcWorkDays_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CalcWorkDays of Module modDateFunctions"
GoTo CalcWorkDays_Exit

End Function

--------------------------------

If you want to include holidays in the count, just comment out this line:

CalcWorkDays = CalcWorkDays - DCount("*", "dbo_holiday_list",
"[holidate] between #" _
& dtmStart & "# And #" & dtmEnd & "#")


--
Dave Hargis, Microsoft Access MVP

My main table is generated via a pass through query and contains a root cause
field with a code value "TBD". These TBDs should be populated with a valid
root cause within 3-days. Our mainframe database stores the date the table
was updated. However, I do not know how to go about designing a process to
determine if the 3-day period has been met.... while excluding weekends.

Would it be best to create a new table with the updated fields? Should I
store the incident number then do a passthrough query to check to see if
these TBDs have been updated? What would be the best way to design this
process in which I can not only determine if the 3-day period has been met
and update my main data table in my Access database to reflect the new
values? My biggest delimma is the exclusion of the weekends. Any advice you
have would be greatly appreciated!