Nested Do While Loops

[richiepr77]

I'm trying to insert records in a table with a range of dates. I've already did that with one employee on this sub.

DoCmd.SetWarnings False

Dim FirstDate, EndDate As Date, RecordNo As Long, ChangeType As String

FirstDate = Me.Date1
EndDate = Me.Date2
RecordNo = 1
ChangeType = cboTipoCambio.Value


Do While FirstDate <= EndDate

    If Weekday(FirstDate, vbSunday) <> vbSaturday And Weekday(FirstDate, vbSunday) <> vbSunday Then

        DoCmd.RunSQL "INSERT INTO tblMonth (RecordNo, Date, Status) VALUES (" & RecordNo & ",#" & Format(FirstDate, "mm/dd/yyyy") & "#,'" & ChangeType & "')"

    End If

     FirstDate = DateAdd("d", 1, FirstDate)

Loop

End Sub

I tried to do it with nested loops, but it runs only one record.

DoCmd.SetWarnings False

Dim FirstDate, EndDate As Date, RecordNo As Long, ChangeType As String

FirstDate = Me.Date1
EndDate = Me.Date2
RecordNo = 1
ChangeType = cboTipoCambio.Value


Do While RecordNo <= 366

Do While FirstDate <= EndDate

    If Weekday(FirstDate, vbSunday) <> vbSaturday And Weekday(FirstDate, vbSunday) <> vbSunday Then

        DoCmd.RunSQL "INSERT INTO tblMonth (RecordNo, Date, Status) VALUES (" & RecordNo & ",#" & Format(FirstDate, "mm/dd/yyyy") & "#,'" & ChangeType & "')"

    End If

    FirstDate = DateAdd("d", 1, FirstDate)

Exit Do

RecordNo = RecordNo + 1

Loop

End Sub

What I'm doing wrong?

 

[MajP]

You exit the do before updating the record number, so each record gets the same record number.

Remove the exit do

 

[MajP]

After relooking at it you are not closing the second loop

do while
do while
loop
loop

 

[richiepr77]

Removed the Exit Do.
Closed the second Loop. (Thanks for that, didn't see it)

Here's the corrected code:

DoCmd.SetWarnings False

Dim FirstDate, EndDate As Date, RecordNo As Long, ChangeType As String

FirstDate = Me.Date1
EndDate = Me.Date2
RecordNo = 1
ChangeType = cboTipoCambio.Value


Do While RecordNo <= 366

Do While FirstDate <= EndDate

    If Weekday(FirstDate, vbSunday) <> vbSaturday And Weekday(FirstDate, vbSunday) <> vbSunday Then

        DoCmd.RunSQL "INSERT INTO tblMonth (RecordNo, Date, Status) VALUES (" & RecordNo & ",#" & Format(FirstDate, "mm/dd/yyyy") & "#,'" & ChangeType & "')"

    End If

    FirstDate = DateAdd("d", 1, FirstDate)

Loop

RecordNo = RecordNo + 1

Loop

End Sub

but it brings me only one RecordNo with the range of dates.



This is the final table I want to reach: (Let's say all records with Status A between the dates of 12/13/2010 and 12/15/2010)

RecordNo Date Status
1 12/13/2010 A
1 12/14/2010 A
1 12/15/2010 A
2 12/13/2010 A
2 12/14/2010 A
2 12/15/2010 A

 

[MajP]

after you run through the inner loop the first date will equal the inner date and the inner loop will never run again.

do Do While RecordNo <= 366
FirstDate = Me.Date1
EndDate = Me.Date
do while firstdate <= enddate
loop
'after the first loop first date will = end date
'Reset the dates before reentering the loop
loop

 

[MajP]

should say:
after you run through the inner loop the first date will equal the end date...

 

[MichaelRed]

I Think some further suprises are in the future.

The outter loop seems like it will execute 365 thmes, somewhat different than the sample posted (e.g. RecNo is initalized as 1, and the literal value for the loop counter end value is < 366)?

Further, you show three records per pass of hte inner loop, however incrementing the start value of the loop would appear to diminish the loop count?



MichaelRed