Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working Days (2000)

    Im trying to use the following code to determine the number of Working Days between 2 dates. It removes bank holidays from a pre-determined table.

    On my form i have a text box with this as the control source :- <font color=blue>=Deltadays(1/1/2003,14/3/2003)</font color=blue>

    This is returning a '0' (zero) as the result.

    Ideally i want to call this from a query, presumably like this? : - <font color=blue>Days: Deltadays([startdatefromquery],[enddatefromwhereever])</font color=blue>
    Code:-
    <font color=red>---------------------------------------------------------------------------------------------------------------------------------------</font color=red>

    <font color=blue>Public Function Deltadays(StartDate As Date, EndDate As Date) As Integer
    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
    Dim dbs As Database

    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
    Case Is = 7 ' Saturday
    ' do nothing
    Case Else ' Normal Workday
    strCriteria = "[HoliDate] = " & NumSgn & MyDate & NumSgn
    rstHolidays.FindFirst strCriteria
    If (rstHolidays.NoMatch) Then
    NumDays = NumDays + 1
    Else
    ' do nothing
    End If
    End Select

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

    Deltadays = NumDays

    End Function</font color=blue>
    <font color=red>---------------------------------------------------------------------------------------------------------------------------------------</font color=red>

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

    Re: Working Days (2000)

    It should work in a query the way you describe.

    In your test text box, try putting # signs before and after the dates: =DeltaDays(#1/1/2003#,#14/3/2003#)
    Otherwise, Access probably interprets / as division.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Working Days (2000)

    Another alternative is to wrap each date in the CDate() function instead of typing in pound signs (#).
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working Days (2000)

    Hans,
    As usual, you hit the nail on the head, your a <img src=/S/king.gif border=0 alt=king width=21 height=22>
    If i run into problems with passing the dates to a query from a form, i will try charlotte's suggestion as well.

    changing the code to <font color=blue>=Deltadays(#02/12/2002#,#25/01/2003#) </font color=blue> fixes the problem.

    Summary:

    <font color=blue>The code above is faq 181-261 from another inferior <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> website http://www.tek-tips.com/
    and is freely available for use.
    You can set up a table with every holiday from 2002-2004 (or more), and the code will eliminate these days from the total days.
    For working out % of attendance its just right </font color=blue>
    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

Posting Permissions

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