Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: Problem with Do Statement - Recurring Schedule Entries

    I am using the code below to schedule recurring events eother every 7. 14 or 21 days
    the case below assumes it is every 7 days


    If I enter a start date of 5/1/06
    and an end date of 7/31/06

    I should get weekly entries until 7/31/06

    It works well, however. the last entry is dated August, 7, 2006 - which is after the end date

    How can I limit the entries to be less than or = to the end date?


    the entry before August 7, 2006 is July 31, 2006 (where it should end)


    Dim iDate As Date
    Dim zDate As Date
    Dim sBiDate As Date
    Dim eBiDate As Date
    Dim day2Date As Date
    Dim dayNum As Integer


    iDate = StartDate




    If Me.Frequency = 7 Then

    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,S erviceID,Amount,CustomerNo) " & _
    " Values(" & Me.ScheduleID & ",#" & StartDate & "#," & Me.CrewNo & "," & Me.ServiceID & "," & Me.txtLCPrice & "," & Me.CustomerNo & ")")




    Do While iDate <= EndDate



    zDate = DateAdd("d", 7, iDate)
    CurrentDb.Execute ("Insert into tblScheduleDetail(ScheduleID,ScheduleDate,CrewNo,S erviceID,Amount,CustomerNo) " & _
    " Values(" & Me.ScheduleID & ",#" & zDate & "#," & Me.CrewNo & "," & Me.ServiceID & "," & Me.txtLCPrice & "," & Me.CustomerNo & ")")

    iDate = zDate


    Loop

    end if

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    ab,

    I notice that StartDate and EndDate are not declared (dimmed), which means that they can have any value. I betcha your enddate has a time component in it (say 10:00 AM), so when your code compares idate (dimmed as a pure date, no time component - that means 12:00 midnight) to enddate, idate is less even at 7/31/2006 - by 10 hours. Try dimming StartDate and EndDate as date types, and I betcha your problem goes away.

    Sam

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    That's because your logic is wrong ... or mostly but not quite right. HINT: take the "=" for your Do While ...


    Your loop works on the next week (insertion wise that is). So, when you get to 7/31 it meets the condition and writes out the 8/7 record ... 7/31 is written on the loop prior ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Not it at all Sam ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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