Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: recurring schedule records for specified day of week...

    I am using the code below to generate recurring schedule records

    iDate is a Start Date while EndDate is an endingdate

    if iDate = 2/16/06 - which is ona Thursday and EndDate = 12/31/06 then
    I want to loop thru and create a record for each Thursday from 2/16/06 to 12/31/06

    using DateAdd("d", 7, iDate) adds 7 days to the Begin Date - but I am getting records that do not appear on a Thursday...

    How can I be sure to generate records on the same day every week as the initial start day (in the case above Thursday)

    Any help is appreciated!

    Code Below........................

    Do While iDate < EndDate

    zDate = DateAdd("d", 7, iDate)
    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,C ustomerID) " & _
    " Values(" & Me.ScheduleID & ",#" & zDate & "#," & Me.CrewNo & "," & Me.CustomerID & ")")

    iDate = zDate


  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    I don't why that didn't work. I just did a quick test and every date created was a Thursday.

  3. #3
    Join Date
    Nov 2003
    Of Hand...I don't see anything wrong with your code. It should produce a Date which would be a Thursday of every week up to the EndDate. To test this simply run this code:

    Dim iDate As Date, EndDate As Date
    Dim zDate As Date
    iDate = #2/16/2006#
    EndDate = #12/31/2006#
    Do While iDate < EndDate
       zDate = DateAdd("d", 7, iDate)
       MsgBox Format(zDate, "dddd") & "    " & zDate
       iDate = zDate
    The Message Box will display the date for every Thursday up to the EndDate.

    The SQL Statement you're using appears to be OK but never the less there may be a issue there. Perhaps a Null where there shouldn't be.

    Self Taught In ALL Environments.....And It Shows!

Posting Permissions

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