Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    24

    Unanswered: CalcworkDays - add criteria

    I'm using the CalcWorkDays function (below) in a few of my queries and it's working fine.

    Problem: I want to filter out some of records by adding criteria to the expression...but it errors out saying too complex.

    Example:
    DaystoShip: CalcWorkDays([Order Date],[Ship Date])
    Criteria: >2 (only want to see records where CalcWorkDays is greater than 2. This errors out.)

    Can I accomplish the same thing by modifying the code below? If so, how?

    Code:
    Public Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
      
    On Error GoTo CalcWorkDays_Error 
      
    'Calculates the number of days between the dates 
    'Add one so all days are included 
    CalcWorkDays = DateDiff("d", dtmStart, dtmEnd) - _ 
    (DateDiff("ww", dtmStart, dtmEnd, vbSaturday) + _ 
    DateDiff("ww", dtmStart, dtmEnd, vbSunday)) 
      
    'Fix bug found where months starting on Saturday or Sunday always over state by one day 
    If Weekday(dtmStart, vbMonday) > 5 And Day(dtmStart) = 1 Then
    CalcWorkDays = CalcWorkDays - 1 
    End If
      
    'Subtract the Holidays 
    CalcWorkDays = CalcWorkDays - DCount("*", "tblHoliday", "[holdate]between #" & dtmStart & "# And #" & dtmEnd & "#") 
      
    CalcWorkDays_Exit: 
      
    On Error Resume Next
    Exit Function
      
    CalcWorkDays_Error: 
      
    MsgBox "Error " & Err.Number & " (" & Err.Description & _ 
    ") in procedure CalcWorkDays of Module modDateFunctions"
    GoTo CalcWorkDays_Exit 
      
    End Function

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in my books for this sort of thing you'd be better of with a calendar table
    calendar
    year
    month
    31 x day (an integer)

    say your calendar starts on 01 jan 2011
    Code:
            S S M T W T F S S  M  T  W  T  F  S  S  M  T  W  T  F  S  S  M  T  W  T  F  S  S  M 
    year M  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
    2011 01 1 1 1 1 2 3 4 5 5 5  6  7  8  9  10 10 10 11 12 13 14 15 15 15 16 17 18 19 20 20 20
    Bearing in mind that in the UK the 3rd of January is a bank holiday, however in scotalnd 4th is also a bank holiday and hasn't been defined here
    you need to decide if you want to store weekend & holidays as the previous index day or the following index day. historically I've used the next working day, hence why 1 repeats for the first 4 days of January, as we don't start work till the 4th.
    as the table is defiend as a 31 day table, you just fill in the missing days and treat them as if they are non working days (eg 29,39,31 of Feb, 31st April and so on)
    then to find the working day you retrieve the relevant column for that year and month.
    to forecast the day an event will complete you need to know its start date, and the duration of the event and then find the last occurance of thant id int he table

    so say the job starts on the 11th of January and takes 6 days
    11th jan = day 6, add 6 days , so look for id of 12
    so retireve the row whose day ID is 12 retrieve the row whose first day >= 12 and last day is <= 12, then scan through the list backwards (assuming you have stored the next working day) or forwards if you stored the previous workign day.

    an alternative older implementation had a string containing 31 5 digit numbers
    00001000010000100001000020000300004
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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