The dates have to come from somewhere.
Create a table with just one Number field named (say) CountID. Save it as
tblCount. Enter numbers from 0 to the maximum number of months back you have
to go - 100, 1000 whatever.
Create a query using your existing table and tblCount. Make sure there is no
line joining the 2 tables in the upper pane of the query design window.
In a fresh column of the Field row in query design, enter:
DateAdd("m", tblCount.CountID, [YourDateField])
In the Criteria row beneath this, enter:
<= Date()
For every record in your query, you will now have a row for each month
between YourDateField and today.
You can now use this query as the source for your report.
This technique is called a Cartesian Product. If there is no join between 2
tables, the query gives you every combination of the two. We then used the
criteria to filter just the ones we wanted.
If you want to enter the 1000 numbers programmatically:
Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long
Set rs = DBEngine(0)(0).OpenRecordset("tblCount", dbOpenDynaset)
For lng = 0 To HowMany - 1
rs.AddNew
rs![CountID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function
