Start and End Date Count Records Open per Day

[mikerea90]

Okay I changed it to this:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
CurrentDb.Execute "DELETE ReportDay FROM tblReportDate"
Dim rsReser As DAO.Recordset
Set rsReser = CurrentDb.OpenRecordset("tblReportDate", dbOpenDynaset, dbSeeChanges)

Do While [frmReports]![StartDate] <= [frmReports]![EndDate]

Loop

Dim stDocName As String
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview
Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub

But I get "Cannot find the expression "l" "

 

[mikerea90]

I meant to say: " cannot find the field "l" "

 

[pbaldy]

The For/Next loop was something necessary for what I was doing in my example, it was not relevant to you. I also suggested using variables. Try this, which I cleaned up a bit:

  Dim dteStart      As Date
  Dim dteEnd        As Date
  Dim stDocName     As String
  Dim rsReser       As DAO.Recordset

  On Error GoTo Err_Command5_Click

  CurrentDb.Execute "DELETE * FROM tblRecordDate"
  Set rsReser = CurrentDb.OpenRecordset("tblRecordDate", dbOpenDynaset, dbSeeChanges)

  dteStart = Forms!frmReports.StartDate
  dteEnd = Forms!frmReports.EndDate

  Do While dteStart <= dteEnd
    rsReser.AddNew
    rsReser("ReportDay") = dteStart
    rsReser.Update

    dteStart = DateAdd("d", 1, dteStart)
  Loop

  stDocName = "Report1"
  DoCmd.OpenReport stDocName, acPreview
  
Exit_Command5_Click:
  Set rsReser = Nothing
  Exit Sub
Err_Command5_Click:
  MsgBox err.Description
  Resume Exit_Command5_Click

 

[mikerea90]

Okay I am getting an error with my sub not being defined. And thank you for your patience:

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim dteStart      As Date
  Dim dteEnd        As Date
  Dim stDocName     As String
  Dim rsReser       As DAO.Recordset
  CurrentDb.Execute "DELETE * FROM tblReportDate"
  Set rsReser = CurrentDb.OpenRecordset("tblReportDate", dbOpenDynaset, dbSeeChanges)
  dteStart = Forms!frmReports.StartDate
  dteEnd = Forms!frmReports.EndDate
  Do While dteStart <= dteEnd
    rsReser.AddNew
    rsReser("ReportDay") = dteStart
    rsReser.Update
    dteStart = DateAdd("d", 1, dteStart)
  Loop
  stDocName = "Report1"
  DoCmd.OpenReport stDocName, acPreview
Err_Command5_Click
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
Exit_Command5_Click:
  Set rsReser = Nothing
  Exit Sub

 

[pbaldy]

Something got screwed up with your copy/paste. The exit and err portions at the end are reversed, and there's no colon after the err portion, which is probably the cause of the error. Just copy/paste the whole bit in again.

 

[mikerea90]

Private Sub Command5_Click()
Dim dteStart As Date
Dim dteEnd As Date
Dim stDocName As String
Dim rsReser As DAO.Recordset
On Error GoTo Err_Command5_Click

CurrentDb.Execute "DELETE * FROM tblReportDate"
Set rsReser = CurrentDb.OpenRecordset("tblReportDate", dbOpenDynaset, dbSeeChanges)
dteStart = Forms!frmReports.StartDate
dteEnd = Forms!frmReports.EndDate
Do While dteStart <= dteEnd
rsReser.AddNew
rsReser("ReportDay") = dteStart
rsReser.Update
dteStart = DateAdd("d", 1, dteStart)
Loop
stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub
Exit_Command5_Click:
Set rsReser = Nothing
Exit Sub

 

[mikerea90]

Private Sub Command5_Click()

Dim dteStart As Date
Dim dteEnd As Date
Dim stDocName As String
Dim rsReser As DAO.Recordset

On Error GoTo Err_Command5_Click

CurrentDb.Execute "DELETE * FROM tblReportDate"
Set rsReser = CurrentDb.OpenRecordset("tblReportDate", dbOpenDynaset, dbSeeChanges)

dteStart = Forms!frmReports.StartDate
dteEnd = Forms!frmReports.EndDate

Do While dteStart <= dteEnd
rsReser.AddNew
rsReser("ReportDay") = dteStart
rsReser.Update
dteStart = DateAdd("d", 1, dteStart)

Loop

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub

Exit_Command5_Click:
Set rsReser = Nothing
Exit Sub

There we go, easier to read I think...

 

[pbaldy]

You haven't said what's happening, but you can't have copied/pasted what I posted because the err and exit portions are still reversed.

 

[mikerea90]

I apologize - I tried to edit it and I moved some stuff around. I am still learning how to use Visual Basic and I thought this was the correct order. I will rearrange and repost..

 

[mikerea90]

Private Sub Command5_Click()

Dim dteStart As Date
Dim dteEnd As Date
Dim stDocName As String
Dim rsReser As DAO.Recordset

On Error GoTo Err_Command5_Click

CurrentDb.Execute "DELETE * FROM tblReportDate"
Set rsReser = CurrentDb.OpenRecordset("tblReportDate", dbOpenDynaset, dbSeeChanges)

dteStart = Forms!frmReports.StartDate
dteEnd = Forms!frmReports.EndDate

Do While dteStart <= dteEnd
rsReser.AddNew
rsReser("ReportDay") = dteStart
rsReser.Update
dteStart = DateAdd("d", 1, dteStart)

Loop

stDocName = "Report1"
DoCmd.OpenReport stDocName, acPreview
Exit_Command5_Click:
Set rsReser = Nothing
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub

 

[mikerea90]

That solved the problem. Thanks again Paul!

 

[mikerea90]

Another question though - how would I be able to make is so that the dates only populate once every 4 days?

Like 5/1/09, 5/5/09, 5/9/09, 5/13/09 .... 5/29/09

Thanks a lot! Not sure how I'd be able to do this without your help! I'll play with it in the mean time.

 

[mikerea90]

Nevermind - I just changed the 1 to a 4... in DateAdd()