Generate dates in a calendar table

Lambel
Lambel used Ask the Experts™
on
I want to generate a calendar in an Access database that goes out 3 months or up to a year.  I would like to populate a table with a record for each day, and identify if the day is the first day of the month or the last day of the month (possibly with a yes/no flag).  

I hope to then use this calendar to project out a home budget and indicate cash flow based on current bank balance and upcoming paydays.

I'm stumped how to generate the table.  Any advice?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
first create a table tblDates with two fields dDate Date/Time, FirstLast  Yes/No

place this codes in a regular module, call the sub createDates, the table tblDates will be populated with dates from 1/1/2010 to 12/31/2010 with indicator for the firstLast date of themonth


Sub createDates()
Dim j As Integer, vDate As Date, rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblDates")
For j = 1 To 12
    For vDate = DateSerial(Year(Date), j, 1) To DateSerial(Year(Date), j + 1, 0)
        With rs
            .AddNew
            !ddate = vDate
                If vDate = DateSerial(Year(Date), j, 1) _
                    Or vDate = DateSerial(Year(Date), j + 1, 0) Then
                    !FirstLast = -1
                End If
            .Update
        End With
    Next
Next
rs.Close
End Sub

Commented:
Sorry for the delay....I haven't been able to get this to work yet.  I appreciate your effort.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial