Re: Generate Range of Dates using Access VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


--
Email address is not valid.
Please reply to newsgroup only.


"Marshall Barton" wrote:

robboll wrote:

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


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

--
Marsh
MVP [MS Access]

.

Relevant Pages

  • Re: Transform using DoCmd.RunSQL fails
    ... John Spencer wrote: ... You might have to create a recordset and then populate the table by stepping ... Alternative would be to create the crosstab query and save that then use the ...
    (microsoft.public.access.queries)
  • Re: Combo Box Limit of 20 Fields
    ... record from a quey an use the columns to populate the controls on your form. ... Is the query based on the recordset of the form or is it from a different ...    With rst ...
    (microsoft.public.access.forms)
  • Re: Combo Box Limit of 20 Fields
    ... record from a quey an use the columns to populate the controls on your form. ... Is the query based on the recordset of the form or is it from a different ...    With rst ...
    (microsoft.public.access.forms)
  • RE: ComboBox Filled by Query
    ... Yes this should work whether you get the data from Access or SQL Server. ... I need to load the Excel Combo Box with a Recordset ... >> Yes you can fill the combobox with a query. ... >>> ListFillRange property to populate the combobox? ...
    (microsoft.public.excel.programming)
  • Passing 2 parameters to stored query?
    ... query from a form. ... populate the variables then open the query it ... Set rst = New ADODB.Recordset ... Do Until CalcDate = Me.txtDateTo ...
    (microsoft.public.data.ado)