Generate Range of Dates using Access VBA

  • Thread starter Thread starter robboll
  • Start date Start date
R

robboll

In Excel 2003 if you put a Date in a cell, you can drag that value
down through the lower cells; and with each cell you pass the date is
incremented by one. I know that I could then import this data into an
Access database.

What is a good method of doing this in exclusively in Access if I want
the resulting table to be populated with the date range: 01/01/2007
through 05/15/2007

TableName: Table1
FieldName: MyDates

Any VBA suggestions appreciated!

RBolling
 
You can use a 'driver' table, a table with one field, its primary key, say
iota, with values from 0 to 999 (or whatever). Let us call that table Iotas,
then:


SELECT startingDate + Iotas.iota
FROM Itoas
WHERE iotas.iota<= endingDate - startingDate



will do.


Vanderghast, Access MVP
 
robboll said:
In Excel 2003 if you put a Date in a cell, you can drag that value
down through the lower cells; and with each cell you pass the date is
incremented by one. I know that I could then import this data into an
Access database.

What is a good method of doing this in exclusively in Access if I want
the resulting table to be populated with the date range: 01/01/2007
through 05/15/2007

TableName: Table1
FieldName: MyDates
Click to expand...


One way that does not use any VBA code is to create a table
named Numbers with one field named Num. Populate it with
values 1, 2, 3, . . . up to more than you will ever need.
Then you can execute the Append query to populate your dates
table:
INSERT INTO datestable
SELECT #1/1/2007# + Num - 1
FROM Numbers
WHERE #1/1/2007# + Num - 1 <= #05/15/2007#

Creating the Numbers table may seem like just shifting your
problem to a different table. However, this technique is
useful in quite a few situations and you may very well need
a numbers table for other things in the future.

Another, less versatile, way is to open a recordset and add
new records:

Set rs = db.OpenRecordset("datestable"
For dt = #1/1/2007# To #05/15/2007#
rs.AddNew
!datefield = dt
rs.Update
Next dt
rs.Close : Set rs = Nothing
 
I use Marshall's technique, but avoid a large table by only including the
values 0-9 in the table. Then I create a query that will generate a large
number of values based on that table. To get values from 0 to 999 I would
use something like:

SELECT Hundreds.intValue * 100 + Tens.intValue * 10 + Ones.intValue
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones
 
Back
Top