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,477
    Provided Answers: 11
    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 16:43.
    hope this help

    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-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  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 12: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
  •