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?
--
Bruce

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

--
Wayne
Manchester, England.



"Bruce" wrote:

> 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?
> --
> Bruce

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

Bruce wrote:
> 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?

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;


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"John Spencer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> Bruce wrote:
>> 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?

On Fri, 7 Nov 2008 09:31:00 -0800, Bruce <(E-Mail Removed)>
wrote:

>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 W. Vinson [MVP]

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

Douglas J. Steele wrote:
> 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 wrote:
> On Fri, 7 Nov 2008 09:31:00 -0800, Bruce <(E-Mail Removed)>
> wrote:
>
>
>>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.

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 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.

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
http://I.Am/DougSteele
(no private e-mails, please)


"John Spencer" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
> Douglas J. Steele wrote:
>> 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;
>>

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


"Wayne-I-M" wrote:

> 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
>
> --
> Wayne
> Manchester, England.
>
>
>
> "Bruce" wrote:
>
> > 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?
> > --
> > 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?
--
Bruce