List of Dates

[Maggie]

I need to make a list of dates from the following:


Field Example

RptStartDate 1/13/06

RptQty 10

RptInterval 5


Do I need more information to work with.


My list should display the dates for the next reports due. Like a payment
table on a loan.


Example Report result:


Report A

Start Date:1/13/06

Reports Due: 10

Yealy Interval: 5


Report Schedule:

1. 1/13/2006

2. 1/13/2011

3. 1/13/2016

4. 1/13/2021

5. 1/13/2026

6. 1/13/2031

7. 1/13/2036

8. 1/13/2041

9. 1/13/2046

10.1/13/2051


End of Report


I am using the DateAdd and DateSerial Functions in the details portion of
the report. They do not give me a "list". The functions use the start date
and the interval just fine, but they do not let me tell it how many times to
do it.


It needs to loop. Is this possible without getting too complicated?


Thanks,

Maggie

 

[Allen Browne]

1. Create a table with one field named CountID, of type Number.
Mark it as primary key.
Save the table as tblCount.

2. Use the function below to populate the table to the highest RptQty you
will ever need. For example to fill it with numbers 0 to 1000, you would use
this in the Immediate Window (Ctrl+G):
? MakeData(1000)

3. Create a table with your original table and tblCount. If you see any line
joining the 2 tables in the upper pane of query design, delete the line. It
is the lack of any join that gives you every combination (a Cartesian
product.)

4. Drag CountID into the query grid. In the Criteria row beneath this,
enter:
<= [RptQty]
This limits the query to the right number of repeats.

5. In a fresh column in the Field row, enter:
ScheduleDate: DateAdd("yyyy", [CountID] * [RptInterval], [RptStartDate])

6. Add whatever other fields you want to the query output grid.

From your example, we assume that the interval is always in years. If you
wanted the flexibility to schecule in months or days as well, you could use
a field for the IntervalType, and use:
ScheduleDate: DateAdd([IntervalType], [CountID] * [RptInterval],
[RptStartDate])

And here's the function to populate your counting table for you:

Function MakeData(HowMany As Long)
Dim rs As DAO.Recordset
Dim lng As Long

Set rs = DBEngine(0)(0).OpenRecordset("MyTable", dbOpenDynaset)
For lng = 0 To HowMany
rs.AddNew
rs![MyID] = lng
rs.Update
Next
rs.Close
Set rs = Nothing
End Function