How to make a table of dates

[Terry]

I have a need to make a table of date (1/1/2008 - 1/1/2018).
All I need is one field with each date for the next 10 years. Can anyone
suggest a simple query that I could run to make such a table so I don't have
to manually type all the dates for the next 10 years?

Any help would be greatly appreciated.

Thanks,
Terry

 

[John W. Vinson]

I have a need to make a table of date (1/1/2008 - 1/1/2018).
All I need is one field with each date for the next 10 years. Can anyone
suggest a simple query that I could run to make such a table so I don't have
to manually type all the dates for the next 10 years?

Any help would be greatly appreciated.

Thanks,
Terry

Well, one could toss together a function, but I'd just use Excel. Open a new
spreadsheet; put 1/1/2008 in A1; select cells A1 through A3654; use Insert...
Series to fill it. Copy and paste into your table.

John W. Vinson [MVP]

 

[Klatuu]

John showed you a how, but I would ask the question Why?
It is not uncommon to store specific dates like holidays in a table so your
app can know about them, but to create a one column table with 10 years worth
of dates is highly suspect.

If you could please tell me what you want to accomplish, maybe I can save
you some work.

 

[fredg]

I have a need to make a table of date (1/1/2008 - 1/1/2018).
All I need is one field with each date for the next 10 years. Can anyone
suggest a simple query that I could run to make such a table so I don't have
to manually type all the dates for the next 10 years?

Any help would be greatly appreciated.

Thanks,
Terry

Did you mean to include 1/1/2018 or up to 12/31/2017?

The create a new table named "tblDates".
Add one field named "Dates", Date datatype.
(Do not use Date as the field name as Date is a reserved keyword.)

Then Copy and paste this function into a Module:

Public Sub FillDates()
' Will fill a Date Field with consecutive date values.

Dim Db As DAO.Database
Dim rs As Recordset
Dim dteFrom As Date
Dim dteTo As Date
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblDates")
dteFrom = #1/1/2008#
dteTo = #1/1/2018#
With rs
Do Until dteFrom = dteTo + 1 ' (the +1 will include 1/1/2018)
.AddNew
!Dates = dteFrom
.Update
dteFrom = dteFrom + 1
Loop
End With
Set Db = Nothing
Set rs = Nothing
End Sub

Run the function by clicking on the Run sub/userform tool button, or
by pressing F5.

Change the table and field names if necessary.

 

[Terry]

Thanks so much for your suggestion. I really appreciate your help.

Terry

 

[Terry]

Thanks Fred: This is just what I was looking for.

Terry