Table containing all dates in the year! 1

[terre]

This should be so simple.....why is there a "block"?

Simply want to fill a table, tblDates, with all the dates in the year....from 1/1/2007 until 31/12/2007.

And, by clicking a command button, appending all next years dates, etc.

tblDate, has only 2 fields.....DateID,Date

Anyone able to help?

thx

 

[PHV]

A starting point (VBA code):
Dim dtDate As Date
dtDate = #2007-01-01#
While dtDate <= #2007-12-31#
CurrentDb.Execute "INSERT INTO tblDate ([Date]) VALUES (#" & Format(dtDate, "yyyy-mm-dd") & "#)")
dtDate = dtDate + 1
Wend

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[terre]

Wow! That easy....thank you so much

Terre

 

[terre]

Should mention, in case others are looking......


Dim dtDate As Date
dtDate = #2007-01-01#
While dtDate <= #2007-12-31#
CurrentDb.Execute "INSERT INTO tblDate ([Date]) VALUES (#" & Format(dtDate, "yyyy-mm-dd") & "#)"
dtDate = dtDate + 1
Wend

That is.....there is an excess ( at the end of the currentdb.execute statement.

thanx again PHV

 

[PHV]

OOps, sorry for the typo :~/

 

[MichaelRed]

on a broader scope, one has to wonder why? seems to be easy to know the dates within any (reasonable) range without explicitly setting forth into a data set w/o additional information. in most instances of collections of singular data, the common rationale is to idnetify subsets of the data points for specific purposes (inclusion / exclusion from a set). this is mostly always done by just having the data set reduced to the subsets requiring the identification. an example might be to identify the holidays within a specific period. In such instances, there is little point to listing all of the dates within the range. this 'exercise', at least as noted in the original post, doesn't go so far as to identify a subset, so one is compelled to question the purpose, mayhap to learn anew ...




MichaelRed

 

[AlexCuse]

Golom offers a neat solution to this problem here: thread701-1318398

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom