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
|
|||
|
|||
Help modifying a "working days" query/function
All of my attempts at modifying this function have failed, so I am
hoping someone here can give me some direction. Basically, you take today's date, and find out what the date would have been 90 working days ago. Now, by working days, I don't mean standard working days (M-F). We work some Saturdays, so what I did was create a table called tNonWorkingDays that has one column called NonWorkingDays. The data in the table is just a list of dates that we didn't work (every Sunday, every holiday, and the Saturdays that we didn't work) So, I basically need to count back 90 days from today, plus 1 day for every non-working day contained in the table. Then, I can query my data based on the date returned through today's date. The function that I found lets me pass a starting date and an ending date, and it returns the number of working days. I think it is counting Saturdays and Sundays as non working days by default though. It should be a simple mod to get what I need, but I can't figure it out. Any help is appreciated. If you need more info, please let me know. TIA, Tim Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [NonWorkingDate] FROM tNonWorkingDates", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate = EndDate rst.FindFirst "[NonWorkingDate] = #" & StartDate & "#" If Weekday(StartDate) vbSunday And Weekday(StartDate) vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function |
#2
|
|||
|
|||
Help modifying a "working days" query/function
Assuming you already have a function that efficiently compute the number of
working days between two dates, the following algorithm can be use: guess1= today + 90 ' guess that 90 working day, from now, will be today+90 error1=90-MyFunction( today, guess1) ' ie, we are short of how many days, with that guess guess2=guess1+error1 error2=90-MyFunction(today, guess2) etc., until the error =0 where MyFunction is the function returning the number of working days between the two given dates. Sure, if that function just 'count one by one' the days, the proposed algorithm is quite inefficient. Then, try: wanted =90 actual = today A0: actual = actual + 1 if( actual is a working day) then wanted = wanted-1 end if if( wanted =0 ) then return the value of actual end if go to A0 which also counts the days one by one, but a given day will be examined just once, in this case. Hoping it may help, Vanderghast, Access MVP "Zeunasc" wrote in message oups.com... All of my attempts at modifying this function have failed, so I am hoping someone here can give me some direction. Basically, you take today's date, and find out what the date would have been 90 working days ago. Now, by working days, I don't mean standard working days (M-F). We work some Saturdays, so what I did was create a table called tNonWorkingDays that has one column called NonWorkingDays. The data in the table is just a list of dates that we didn't work (every Sunday, every holiday, and the Saturdays that we didn't work) So, I basically need to count back 90 days from today, plus 1 day for every non-working day contained in the table. Then, I can query my data based on the date returned through today's date. The function that I found lets me pass a starting date and an ending date, and it returns the number of working days. I think it is counting Saturdays and Sundays as non working days by default though. It should be a simple mod to get what I need, but I can't figure it out. Any help is appreciated. If you need more info, please let me know. TIA, Tim Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [NonWorkingDate] FROM tNonWorkingDates", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate = EndDate rst.FindFirst "[NonWorkingDate] = #" & StartDate & "#" If Weekday(StartDate) vbSunday And Weekday(StartDate) vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function |
#3
|
|||
|
|||
Help modifying a "working days" query/function
In addition to Michel's esteemed advice, you can probably
adapt following (may be off by one day?): '**untested** Public Function fSubtractBusinessDay(pStart As Date, pSub As Integer) 'Subtracts the proper Business days 'skipping days in tNonWorkingDates (NonWorkingDate) 'adapted from code by Arvin Meyer 05/26/98 On Error GoTo Err_fSubBusinessDay Do While pSub 0 pStart = pStart - 1 If DCount("*", "tNonWorkingDates", "[NonWorkingDate]=#" & pStart & "#") = 0 Then 'not an "Off Day" pSub = pSub - 1 End If Loop fSubtractBusinessDay = pStart Exit_fSubBusinessDay: Exit Function Err_fSubBusinessDay: MsgBox Err.Description Resume Exit_fSubBusinessDay End Function "Zeunasc" wrote: All of my attempts at modifying this function have failed, so I am hoping someone here can give me some direction. Basically, you take today's date, and find out what the date would have been 90 working days ago. Now, by working days, I don't mean standard working days (M-F). We work some Saturdays, so what I did was create a table called tNonWorkingDays that has one column called NonWorkingDays. The data in the table is just a list of dates that we didn't work (every Sunday, every holiday, and the Saturdays that we didn't work) So, I basically need to count back 90 days from today, plus 1 day for every non-working day contained in the table. Then, I can query my data based on the date returned through today's date. The function that I found lets me pass a starting date and an ending date, and it returns the number of working days. I think it is counting Saturdays and Sundays as non working days by default though. It should be a simple mod to get what I need, but I can't figure it out. Any help is appreciated. If you need more info, please let me know. TIA, Tim Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [NonWorkingDate] FROM tNonWorkingDates", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate = EndDate rst.FindFirst "[NonWorkingDate] = #" & StartDate & "#" If Weekday(StartDate) vbSunday And Weekday(StartDate) vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function |
#4
|
|||
|
|||
Help modifying a "working days" query/function
I actually got this working in the meantime... Now, I have a function
that will return a date that is 90 days in the past, taking in to consideration anything contained in the tNonWorkingDates table. However, I don't know how to implement this into a query. That is, the query returns the End date, and the Start Date should be today. So, how do I create a query return results limited to the time between these dates (one from a builtin function like Now(), and one from the custom function)? Below is the function that is working. TIA, Tim Public Function basMtgDate() As Date Dim dbs As DAO.Database Dim rstHolidays As DAO.Recordset Dim MyDate As Date Dim MyDays As Long Dim strCriteria As String Dim NumSgn As String * 1 Set dbs = CurrentDb Set rstHolidays = dbs.OpenRecordset("tNonWorkingDates", dbOpenDynaset) startdate = Now() NumDays = 30 NumSgn = Chr(35) If (IsMissing(startdate)) Then startdate = Date End If MyDate = Format(startdate, "Short Date") Do While MyDays NumDays strCriteria = "[NonWorkingDate] = " & NumSgn & MyDate & NumSgn rstHolidays.FindFirst strCriteria If (rstHolidays.NoMatch) Then MyDays = MyDays + 1 Else 'Do Nothing, it is NOT a Workday End If If (MyDays = NumDays) Then Exit Do End If MyDate = DateAdd("d", -1, MyDate) Loop basMtgDate = MyDate End Function |
#5
|
|||
|
|||
Help modifying a "working days" query/function
To use in a query on a date field (say "ProjDate")
whose time portion is not always "00:00:00" to return records for project in last 90 business days... WHERE [ProjDate] = fSubtractBusinessDay(Date(), 90) AND [ProjDate] Date() +1 "Gary Walter" wrote: In addition to Michel's esteemed advice, you can probably adapt following (may be off by one day?): '**untested** Public Function fSubtractBusinessDay(pStart As Date, pSub As Integer) 'Subtracts the proper Business days 'skipping days in tNonWorkingDates (NonWorkingDate) 'adapted from code by Arvin Meyer 05/26/98 On Error GoTo Err_fSubBusinessDay Do While pSub 0 pStart = pStart - 1 If DCount("*", "tNonWorkingDates", "[NonWorkingDate]=#" & pStart & "#") = 0 Then 'not an "Off Day" pSub = pSub - 1 End If Loop fSubtractBusinessDay = pStart Exit_fSubBusinessDay: Exit Function Err_fSubBusinessDay: MsgBox Err.Description Resume Exit_fSubBusinessDay End Function "Zeunasc" wrote: All of my attempts at modifying this function have failed, so I am hoping someone here can give me some direction. Basically, you take today's date, and find out what the date would have been 90 working days ago. Now, by working days, I don't mean standard working days (M-F). We work some Saturdays, so what I did was create a table called tNonWorkingDays that has one column called NonWorkingDays. The data in the table is just a list of dates that we didn't work (every Sunday, every holiday, and the Saturdays that we didn't work) So, I basically need to count back 90 days from today, plus 1 day for every non-working day contained in the table. Then, I can query my data based on the date returned through today's date. The function that I found lets me pass a starting date and an ending date, and it returns the number of working days. I think it is counting Saturdays and Sundays as non working days by default though. It should be a simple mod to get what I need, but I can't figure it out. Any help is appreciated. If you need more info, please let me know. TIA, Tim Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer '................................................. ................... ' Name: WorkingDays2 ' Inputs: StartDate As Date ' EndDate As Date ' Returns: Integer ' Author: Arvin Meyer ' Date: May 5,2002 ' Comment: Accepts two dates and returns the number of weekdays between them ' Note that this function has been modified to account for holidays. It requires a table ' named tblHolidays with a field named HolidayDate. '................................................. ................... On Error GoTo Err_WorkingDays2 Dim intCount As Integer Dim rst As DAO.Recordset Dim DB As DAO.Database Set DB = CurrentDb Set rst = DB.OpenRecordset("SELECT [NonWorkingDate] FROM tNonWorkingDates", dbOpenSnapshot) 'StartDate = StartDate + 1 'To count StartDate as the 1st day comment out the line above intCount = 0 Do While StartDate = EndDate rst.FindFirst "[NonWorkingDate] = #" & StartDate & "#" If Weekday(StartDate) vbSunday And Weekday(StartDate) vbSaturday Then If rst.NoMatch Then intCount = intCount + 1 End If StartDate = StartDate + 1 Loop WorkingDays2 = intCount Exit_WorkingDays2: Exit Function Err_WorkingDays2: Select Case Err Case Else MsgBox Err.Description Resume Exit_WorkingDays2 End Select End Function |
Thread Tools | |
Display Modes | |
|
|