Create Date Table

[Bruce]

I want to create a date table that lists the date (short date format) from a
given date to current. The only field would be the date (say from January 1,
1950) field. Is there a master date table already in existence that I could
edit for the date range for my circumstances?

 

[Wayne-I-M]

Hi Bruce

Sorry to misunderstand.

Why do you want to create a date table with over 21,000 records containing
just a date field

The MUST be a better way to do whatever it is you want to do

Can you give some details

 

[John Spencer]

Create your table and then run code like the following UNTESTED code

Public Sub AddDates(dteStart as Date, dteEnd as Date)
Dim dbAny as DAO.Database
Dim rst as DAO.Recordset
Dim iCount as Long

Set dbany = CurrentDb()
Set rst = dbany.OpenRecordset("SELECT TheDate FROM CalendarTable" & _
" WHERE TheDate is Null")

With rst
For iCount = 0 To DateDiff("d", dteStart, dteEnd)
.AddNew
rst!TheDate = DateAdd("d", iCount, dteStart)
.Update
Next iCount
End With

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

Another alternative is to create 3 tables:

Years
YearNumber - Integer

Months
MonthNumber - Integer

Days
DayNumber - Integer

The Days table will have 31 rows (values 1 through 31), the Months table
will have 12 rows (values 1 through 12) and the Years table will have one
row for each year of interest (values 1950 through 2008?)

Once you have those three tables, you can create a query that will return
one row for each day of interest using the following SQL:

SELECT DateSerial([YearNumber],[MonthNumber],[DayNumber]) AS WhatDate
FROM Days, Months, Years
WHERE (((IsDate([YearNumber] & "-" & [MonthNumber] & "-" &
[DayNumber]))=True))
ORDER BY 1;

 

[John W. Vinson]

I want to create a date table that lists the date (short date format) from a
given date to current. The only field would be the date (say from January 1,
1950) field. Is there a master date table already in existence that I could
edit for the date range for my circumstances?

John's VBA code would do it neatly - but I'm lazy enough I'd just open Excel,
type in a starting date in A1, select column A, Insert... Fill Series, and
select single day. Then copy and paste into my table.

 

[John Spencer]

Douglas,

Why not simplify the query a bit

SELECT Distinct DateSerial([YearNumber],[MonthNumber],[DayNumber]) AS WhatDate
FROM Days, Months, Years

That way you don't need the WHERE clause at all. And probably you don't need
the order by clause either - the distinct will probably do the order by for
you automatically.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[James A. Fortune]

John's VBA code would do it neatly - but I'm lazy enough I'd just open Excel,
type in a starting date in A1, select column A, Insert... Fill Series, and
select single day. Then copy and paste into my table.

I've seen copy and paste a column backfire in Excel 97 and Access 97.
You select the entire column in Excel and paste the result into an
Access table, but sometimes only 40 or so records out of hundreds
selected populate the Access table. I haven't tried it using later
versions of Office.

James A. Fortune
(e-mail address removed)

The main OS's are Mac, Linux and Windows. Phone software seems to be
tracking similar paths and might never settle on a common programming
standard. At least that situation is better than having a different SDK
for each phone manufacturer.

 

[Douglas J. Steele]

Yeah, having the DISTINCT in there will handle the cases like
DateSerial(2009, 2, 29) resulting in 1 Mar, 2009, DateSerial(2009, 2, 30)
resulting in 2 Mar, 2009 and so on.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Why not simplify the query a bit

SELECT Distinct DateSerial([YearNumber],[MonthNumber],[DayNumber]) AS
WhatDate
FROM Days, Months, Years

That way you don't need the WHERE clause at all. And probably you don't
need the order by clause either - the distinct will probably do the order
by for you automatically.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Another alternative is to create 3 tables:

Years
YearNumber - Integer

Months
MonthNumber - Integer

Days
DayNumber - Integer

The Days table will have 31 rows (values 1 through 31), the Months table
will have 12 rows (values 1 through 12) and the Years table will have one
row for each year of interest (values 1950 through 2008?)

Once you have those three tables, you can create a query that will return
one row for each day of interest using the following SQL:

SELECT DateSerial([YearNumber],[MonthNumber],[DayNumber]) AS WhatDate
FROM Days, Months, Years
WHERE (((IsDate([YearNumber] & "-" & [MonthNumber] & "-" &
[DayNumber]))=True))
ORDER BY 1;

 

[Bruce]

Wayne,

I am trying to create a historical table that lists by date the equipment
number (key) and the mileage for that date. For those dates in between the
recorded mileags I want to list the date and then populate the field with the
previous value. I am doing this on existing records, once this is
accomplished a daily query will run caputring the current mileage and
appending to the table. Now I will be able to run mileage and calculation
reports without complicated sub queries (and time consuming. Right now I have
600,000 fuel transactions and when running sub queries it takes a great deal
of time. Therefore if I have a history table of mileages, my calculations can
be based upon the value of a given date.

 

[Bruce]

John,

I have Excel 2002 and attempted to do as you said. I formatted column A as a
short date, entred value 1/1/1960 into A1. Then I selected Insert but the
only options are columns, rows, etc, could not determine how to populate the
remaining fields. Could you be more specific?

 

[John W. Vinson]

John,

I have Excel 2002 and attempted to do as you said. I formatted column A as a
short date, entred value 1/1/1960 into A1. Then I selected Insert but the
only options are columns, rows, etc, could not determine how to populate the
remaining fields. Could you be more specific?

Sorry! Faulty memory:

Edit... Fill... Series.
Select Series In: Columns, Type: Date, and Date Unit: Day.

If the copy and paste doesn't work, File... Get External Data... Import will.

 

[Bruce]

John,

Thank you, used the import feature in Access.