Auto populate table with dates

[Sidran]

I have a table with a few fields, which include a field called StartDate and a field called EndDate. From the table I plan on finding the Min StartDate and the Max EndDate, is it possible, to make a table from these 2 sets of data and have the fields in between be auto populated in increments of a month?

For example. (Dates in dd/mm/yyyy format)
StartDate=1/1/2014 and Max EndDate=1/12/2014
So the table should show:
1/1/2014
1/2/2014
1/3/2014
1/4/2014
1/5/2014
1/6/2014
1/7/2014
1/8/2014
1/9/2014
1/10/2014
1/11/2014


1/12/2014

[June7]

This will require VBA code to create records, like:

Dim newDate As Date
newDate = Me.StartDate
Do Until newDate > Me.EndDate
CurrentDb.Execute "INSERT INTO tablename(datefield) VALUES(#" & newDate & "#")
newDate = newDate + 1
Loop

[Sidran]

Hmmm, figured it would require a loop. What if, instead of adding 1 day to the date, I wanted to add a month to the date? By the way, I started working on a loop before looking at the reply, this is where I've got to:

Dim Db As DAO.Database
Dim Recordset As DAO.Recordset


Set Db = CurrentDb
Set Recordset = Db.OpenRecordset("Monthly Report Hidden Table1") <------ The table that has the field called "Dates"
MinStartDate = Me.MinOfStartDate
MaxEndDate = Me.MaxOfEndDate
While MinStartDate <= MaxEndDate
Recordset.AddNew
Recordset("Dates").Value = MinStartDate
Recordset.Update
Me.MinOfStartDate = DateAdd("m", 1, MinStartDate)

Sadly the last line, where i tried to add a month, didn't work. I am very new to VBA so I doubt my code works. If it doesn't I'll try the one you suggested.

[June7]

Sorry, I forgot you wanted just the first of each month. Just replace 1 line in my code:

newDate = DateAdd("m",1,newDate)

However, you are not showing dates in Access standard.

Review: http://allenbrowne.com/ser-36.html

[Sidran]

Entering this give me an error:

Dim newDate As Date
newDate = Me.MinOfStartDate
Do Until newDate > Me.MaxOfEndDate
CurrentDb.Execute "INSERT INTO "Monthly Report Hidden Table1(Dates) VALUES #" & newDate & "#"
newDate = DateAdd("m", 1, newDate)
Loop
End Sub


In the above code, Monthly Report Hidden Table1 is my table name (yes there are spaces), Dates is the name of the field in the talbe, MinStartDate is the start date and MaxEndDate is the end date.

The error is an 'Expected end of statement'. Removing the speech mark before 'Monthly' results in a syntax error.

About the dates, I did change the setting in the control panel to dd/mm/yyyy and they do appear in my database in the same format. So I doubt that should be the cause of any problem.

[Sidran]

Nevermind the above text. I don't know the reason, but I used your code and instead of using the INSERT INTO, I use the AddNew command to get the job done. Thanks a lot for your help. Seems like you know quite a bit about access cause you seem to be answering everyone problems xD.

[Sidran]

To anyone who is interested in the code that I used:

Dim Db As Database
Dim Recordset As Recordset
Dim MinStartDate As Date


Set Db = CurrentDb
Set Recordset = Db.OpenRecordset("TableName")
MinStartDate = Me.MinOfStartDate


Do Until MinStartDate > Me.MaxOfEndDate
Recordset.AddNew
Recordset("FieldName").Value = MinStartDate
Recordset.Update
MinStartDate = DateAdd("m", 1, MinStartDate)
Loop

[June7]

The correct syntax in my code would be:

CurrentDb.Execute "INSERT INTO [Monthly Report Hidden Table1] (Dates) VALUES(#" & newDate & "#)"

Enclose names with spaces or special characters/punctuation (underscore is exception) with [].