Results 1 to 4 of 4
  1. #1
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215

    Function to Determine Number of Workdays Error

    Hi, I have particular code that is supposed to parse through a table of dates (holidays) to determine how many workdays exist, excluding saturdays and sundays as well.

    It works... but I get an error in bold that says that "RUN TIME ERROR 6: OVERFLOW" and it highlights a particular line below- as you can see.

    Any idea what is causing this?

    BTW. I am running the function through a query, that is, creating a new field in a query that calculates this.


    Option Compare Database



    Function GetWorkDays(dtDateIn As Date, dtDateOut As Date) As Integer




    Dim x As Integer
    Dim rs As Recordset
    Dim db As Database
    Dim strSQL As String
    Dim dtIncrement As Date




    strSQL = "Select HolDate from tblHolidays"
    x = 0


    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)


    dtIncrement = dtDateIn
    Do Until dtIncrement = dtDateOut

    Select Case Weekday(dtIncrement)
    Case vbSaturday


    x = x
    Case vbSunday
    x = x

    Case Else
    rs.FindFirst ("HolDate = # " & Format(dtIncrement, "mm/dd/yyyy") & "#")
    If rs.NoMatch = True Then
    x = x + 1
    End If
    End Select
    dtIncrement = dtIncrement + 1
    Loop


    GetWorkDays = x


    Set rs = Nothing
    Set db = Nothing


    End Function

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    When it errors, what is the value of x?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    McArthurGDM is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2014
    Posts
    215
    It's strange,

    If I go to my locals window - the dates are changed to really bizzarre dates that I didn't intend for. For instance, here is the date increment.

    : dtIncrement : #11/11/2140# : Date

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What are the other date values? My guess is that something wacky is happening and you're overflowing the Integer data type.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 01-31-2014, 01:45 PM
  2. Help with Workdays function (VBA) from Microsoft website
    By GregTheSquarePeg in forum Programming
    Replies: 14
    Last Post: 12-04-2013, 01:50 PM
  3. Workdays Code
    By tomnsd in forum Programming
    Replies: 4
    Last Post: 08-16-2013, 02:30 PM
  4. DateAdd function for workdays?
    By 10 Gauge in forum Programming
    Replies: 2
    Last Post: 04-06-2011, 09:20 AM
  5. Calculating turnaround time on workdays
    By mathonix in forum Queries
    Replies: 1
    Last Post: 04-22-2010, 05:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums