Add a range of dates to a table

[gemadan96]

I want to create a form that ask the user for a start date and an end date. Then when the click OK it will append a record to a table for each date and each date in between based on the increment. For example the start date is 6/20/2014 and the end date is 6/27/2014 and the increment will be 1 day. I want to add records to my table for:



6/20/2014
6/21/2014
6/22/2014
6/23/2014
6/24/2014
6/25/2014
6/26/2014
6/27/2014

I have the form setup to to prompt for the start and end dates. It's the VBA coding to do the dateadd function in a loop I'm trying to figure out.

[June7]

Something like:

Dim dteNew As Date
dteNew = Me.tbxStart
While dteNew < Me.tbxEnd
CurrentDb.Execute "INSERT INTO tablename([datefield]) VALUES(#" & dteDate & "#)"
dteNew = dteNew + 1
Wend

An alternative to the CurrentDb.Execute is to open a recordset object of the destination table and add records to the recordset.

[gemadan96]

Thanks. I was able to figure it out. Here is the code I wrote, which included inserting the weekday name into another field.

Private Sub AddDates_Click()
Dim dbs As Database
Dim strDayName As String
Dim sDate As Date
Dim eDate As Date
sDate = StartDate
eDate = EndDate


Set dbs = CurrentDb
NextDate = sDate


For i = NextDate To eDate
strDayName = WeekdayName(Weekday(NextDate))
strsqlInsert = "Insert into ClassDaysTemp (TempDate, DayName) VALUES (#" & NextDate & "#, '" & strDayName & "')"
dbs.Execute (strsqlInsert)
NextDate = DateAdd("d", 1, NextDate)
Next


End Sub

[Missinglinq]

Another way to skin the cat:

Private Sub cmdGenerateRecords_Click()

Dim i As Integer

If Nz(Me.StartDate, "") = "" Or Nz(Me.EndDate, "") = "" Then
  MsgBox "You Must First Enter Start and End Dates!"
  Exit Sub
End If

i = DateDiff("d", Me.StartDate, Me.EndDate) + 1

For i = 1 To DateDiff("d", Me.StartDate, Me.EndDate) + 1
   DoCmd.GoToRecord , , acNewRec
   Me.YourDateField = Me.StartDate + (i - 1)
  Next i

End Sub

Linq ;0)>