auto fill a date series in make-table query

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Anyone know how I can auto fill a date series using a make-
table query with a start date and Date(). For example,
generate a table starting with an identified start date,
looking something like

7/5/03
7/6/03
7/7/03
7/8/03
7/9/03
7/10/03, etc, up to the present date.

The beggining date will vary each time the make query is
run, but the end date will always be the present date.

Thanks,
Tim
 
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2003# To #12/31/2003#
.AddNew
!WotDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
 
Anyone know how I can auto fill a date series using a make-
table query with a start date and Date().
Click to expand...

An auxiliary table will do this neatly: have a table named Num with
one long integer field N, with values from 0 to the largest number of
days you'll ever need. This table has many uses, I have one in all my
databases now.

Base your MakeTable query on Num; include a calculated field

DateAdd("d", [N], [Enter start date:])

with a criterion of

<= Date()
 
HELP!! IN A HURRY
I know this post is old, but can't find a solution and this post looks like it might work for me. Can someone explain to me what to do with a number table once it is created. (as posted above)
This is what I currently have:
Loan info: Fields - Loan Amount
Date of Loan
Fee Amount
# of months
I need to amortize and have access calculate the monthly date based on the date of the loan.
I have =DateAdd("m",1,[dateofloan]) that does give me the next months date, but I need to have all the dates automatically fill in based on the # of months
It sounds simple but can's get more than 1 record to autofill in the report.
I think a macro might also work, but not sure of the code
 
Back
Top