Populating a ''Calendar'' table

Question

Populating a ''Calendar'' table

Jay Bee-249994

SSCrazy

Points: 2393
More actions
February 12, 2007 at 7:13 am

#72622

Hi all,
Is there a way to populate a 'Calendar' table consisting of a single column with the 'Date' type in Access?
Thanks,

JB.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

Of course - just use the Create Table wizard and create a new table with a single column of type Date. When updating this table, remember that Access requires some modified syntax to recognize a string as a date (for example, #01/01/2007#).

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 2

Hi Tim,

Appreciate that, I wanted to know if there's a way to do the same thing programatically, so that I don't have to manually enter any records??

Cheers,

JB.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

I don't have any written code in access but here's the logic :

set NumbeOfDays = 25000 'your choice here

set StartDate = '2007/01/01 'again when do you need this to start

set i = 0

while i < NumberOfDays

insert into calendar (date) Values dateadd("D", i, StartDate)

i=i+1

wend

There's a set based way to do this but it requires a numbers table.. which you probabley won't have either at this point .

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 4

Hi Ninj,

This is exactly what I wanted!!! Ok, which part of Access must this be implemented in?? Ie, queries, stored procs, VBA, etc??

Cheers,

JB.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

VBA.

BTW my pseudo-code will need a lot of syntax checking .

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 6

Ninj,

These requests are on behalf of a good (DBA) friend of mine who would like me to express his deepest gratitude, it was "Very nice of him, he must be a very good-hearted person indeed!".

And thanks from me too!!

JB

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Glad to help. Come back anytime you need something .