Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Networkdays function forAccess 2000

Networkdays function forAccess 2000

Networkdays function forAccess 2000

I need a function that calculates the workdays between dates. I have one but it does not have the capabilities to insert holidays that fall on a weekday.

In anticipation errolf

RE: Networkdays function forAccess 2000

This appears to be a persistant theme, so if you want a different soloution, use the search function on "Holiday" (or some variation) and seleck entire site/all forums.  Otherwise, this is copied from a previous post'

This is 'My' holidays table(tblHolidays), refered to in the function below:

HoliDate    HoliName

1/1/00            New Year's Day
1/17/00            Martin Luther King Day
2/2/00            Groundhog Day
2/12/00            Lincon's Birthday
2/14/00            Valentine's Day
2/21/00            President's Day
2/22/00            Washington's Birthday
3/8/00            Ash Wednesday
3/17/00            St. Patrick's Day
4/1/00            April Fool's Day
4/20/00            Passover
4/21/00            Good Friday
5/5/00            Cinco de Mayo
5/14/00            Mother's Day
6/11/00            Pentecost
6/18/00            Father's Day
7/4/00            Independence Day
9/4/00            Labor Day
10/31/00    Halloween
11/11/00    Vetran's Day
11/23/00    Thanksgiving
12/25/00    Christmas
12/31/00    New Year's Eve

First, Get the number of days between the dates
NumDays: DeltaDays([StDt], [EndDt])

Public Function DeltaDays(StartDate As Date, EndDate As Date) As Integer

    'Get the number of workdays between the given dates

    Dim dbs As Database
    Dim rstHolidays As Recordset

    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)

    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)
        Select Case (WeekDay(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
                rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays

End Function

There is never time to do it right but there is always time to do it over

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close