Generate Table with Calendar dates to 2020

  • Thread starter Thread starter Snickers
  • Start date Start date
S

Snickers

There has to be a way to easily create a table with calendar days? I just
need a table with dates, nothing fancy.
 
Snickers said:
There has to be a way to easily create a table with calendar days? I just
need a table with dates, nothing fancy.
Click to expand...


SQL can't create a record out of nothing, but it can create a lot of records
out of only a few. Here's a handy trick for creating records, which we can
adapt to your need for a table of dates.

First, create a seed table called "Iotas" (or anything else you like), and
give it only one field, a Long Integer field named "Iota". Enter 10
records, with values for Iota from 0 to 9.

Second, create a query that will generate a conveniently large number of
records from the Iotas table, by including the same table multiple times
without any joins. Use a calculated field to compute an distinct new Iota
value for each record. For example, the SQL below will return 1000 records
with incrementing values for Iota from 0 to 999:

qryIotas1000
------------------
SELECT [A].[Iota]+(10*.[Iota])+(100*[C].[Iota]) AS Iota
FROM Iotas AS A, Iotas AS B, Iotas AS C;

To create a sequence of dates from these records, you could use a query
based on qryIotas1000, with SQL like this:

qrySequentialDates
----------------------------
SELECT CDate([Enter start date])+[Iota] AS CalendarDate
FROM qryIotas1000;

You could use that as the basis of a make-table query or an append query,
applying a filter if you want to restrict the number of dates actually
stored. And, of course, if you want more than 1000 dates, you can modify
qryIotas1000 to create 10,000 records, 100,000 records, etc.
 
Another option would be to write a function, say it's caled FillCalTable,
with parameters for start date and number of days needed. Something like

Public Function(ByRef tblCal as ADODB.Recordset, byval startDate as Date,
byval numDays as Long)

dim dayValue as DAte

For dayValue = startDate to startDate + numDays - 1
tblCal.AddNew
tblCal!DateValue = dayValue
tblCal.Update
Next dayValue

End Function

or something along those lines. I left out creation of the table, not sure
how you might want that handled, depending on whether you'd be creating a new
one, populating an existing one (in which case you may need to make sure it
was empty), etc. This code assumes the table exists already and that you
opened a recordset based on the table.
 
Back
Top