![]() |
If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
![]()
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! |
#2
|
|||
|
|||
![]()
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. -- Kind regards Noƫlla "dtoney" wrote: 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! |
#3
|
|||
|
|||
![]()
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 "dtoney" wrote: 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! |
#4
|
|||
|
|||
![]()
Thanks a bunch! I'll let you know the outcome.
"Klatuu" wrote: 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 "dtoney" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|