Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working Days (Access 2003)

    Came across this code on Post 301993. It's exactly what I need to accomplish in by DB. I am new to VBA and don't understand a couple of things:

    1. I created a holiday table as instructed.
    2. I created another table with both fields: Startdate and Enddate. Question: Should I also include a field called Workingdays2 in my table or should I just create a control on the form and call it Workingdays2?
    3. Do I create a Module or place the Code in the form?
    4. Once I create my form, how do I call in the function? To what control and to what property do I attach the function to and how exactly will I call it in.

    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 [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

    'StartDate = StartDate + 1
    'To count StartDate as the 1st day comment out the line above

    intCount = 0

    Do While StartDate <= EndDate

    rst.FindFirst "[HolidayDate] = #" & 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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working Days (Access 2003)

    2. No, you shouldn't create a WorkingDays field. Since the number of working days is derived information, it shouldn't be stored in a table, but calculated in a query, form or report based on the table.

    3. The WorkingDays2 function is intended for general use, so you should place it in a standard module, not in a form module.

    4. You have (basically) two options:

    a) Create a query based on your table.
    Add the StartDate and EndDate fields to the query grid.
    Enter the following expression in the first empty column:

    WorkingDays: WorkingDays2([StartDate],[EndDate])

    You can then use the query as record source for your form. The WorkingDays field will be available just like any other field.

    - or -

    Create a form based on the table.
    Place a text box on the form with Control Source

    =WorkingDays2([StartDate],[EndDate])

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 2003)

    I have an additional question regarding WorkingDays, which ties into this earlier post.

    I have a field called [Application Date] I need to add 15 days to this field ([Application Date] +15) This Date has to take the nonworkingdays and holidays into account and return a date. How can I modify the below function to do this?

    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 [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

    'StartDate = StartDate + 1
    'To count StartDate as the 1st day comment out the line above

    intCount = 0

    Do While StartDate <= EndDate

    rst.FindFirst "[HolidayDate] = #" & 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

    Thank you for your help.

  4. #4
    New Lounger
    Join Date
    Nov 2004
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 2003)

    Thanks, Hans. It works! You're the best.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working Days (Access 2003)

    Here is such a function:

    Public Function AddWorkDays(StartDate As Date, NumDays As Integer) As Date
    '................................................. ...................
    ' Name: AddWorkDays
    ' Inputs: StartDate As Date
    ' NumDays As Integer
    ' Returns: Date
    ' Note that this function has been modified to account for holidays.
    ' It requires a table named tblHolidays with a field named HolidayDate.
    '................................................. ...................

    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Dim dtmCurr As Date
    Dim intCount As Integer

    On Error GoTo ErrHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)

    intCount = 0
    dtmCurr = StartDate

    Do While intCount < NumDays
    dtmCurr = dtmCurr + 1
    If Weekday(dtmCurr, vbMonday) < 6 Then
    rst.FindFirst "<!t>[HolidayDate]<!/t> = #" & Format(dtmCurr, "mm/dd/yyyy") & "#"
    If rst.NoMatch Then
    intCount = intCount + 1
    End If
    End If
    Loop

    AddWorkDays = dtmCurr

    ExitHandler:
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

    Use it like this in the control source of a text box on a form or report

    =AddWorkDays([Application Date],15)

    or like this in a query:

    NextDate: AddWorkDays([Application Date],15)

  6. #6
    New Lounger
    Join Date
    Nov 2004
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (Access 2003)

    That worked! Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •