Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21

    Unanswered: Update # of Days without Weekends in Table

    Hello, I am trying to loop through a table to have the difference between the AssignedDate and CompletionDate assigned to TimeToComplete. I want the TimeToComplete to ignore weekends and holidays. I have a table (tbl_Holiday) that has a column with every day though 12/31/2010 and a column that has a check mark (yes) for weekends and holidays. When the code runs, I get the following error: Runtime error '2001': You canceled the previous operation. The problem occurs when trying to assign a Dcount value to WorkingDays.

    Here is my code:

    Private Sub Form_Load()

    Dim DB As Database, RS As Recordset
    Dim StartDate As Date, EndDate As Date, WorkingDays As Integer

    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset("tbl_Work", dbOpenDynaset)

    RS.MoveLast
    RS.MoveFirst

    StartDate = RS("AssignedDate").Value
    EndDate = RS("CompletionDate").Value

    Do While Not RS.EOF
    RS.Edit
    WorkingDays = DCount("Day", "tbl_Holiday", "Day BETWEEN StartDate AND EndDate AND NonWrkDay = FALSE")
    RS("TimeToComplete").Value = WorkingDays
    RS.Update
    RS.MoveNext
    Loop

    RS.Close

    cldCalendar.Value = Now
    DoCmd.GoToRecord , , acNewRec

    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You're passing a literal string. No date is LITERALLY between the WORD StartDate and EndDate, hopefully that points you in the right direction...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    change the

    WorkingDays = DCount("Day", "tbl_Holiday", "Day BETWEEN StartDate AND EndDate AND NonWrkDay = FALSE")

    to

    it doesn't now what the startdate / enddate is


    WorkingDays = DCount("Day", "tbl_Holiday", "Day BETWEEN #" & StartDate & "# AND #" & EndDate & "# AND NonWrkDay = FALSE")


    I have use this one it work well

    http://www.mvps.org/access/datetime/date0006.htm
    Last edited by myle; 01-19-06 at 17:43.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jan 2006
    Location
    Mount Wolf, PA
    Posts
    21
    StePhan, this solved my issue. Thank you very much for your help!
    Last edited by em2525; 01-20-06 at 13:39.

Posting Permissions

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