How to populate calendar table/query

  • Thread starter Thread starter jkochchs
  • Start date Start date
J

jkochchs

To manage activities in a project, I need to record events
(e.g., hours spent), per day to lateron perform
calculations, e.g., utilization, billing, etc.

So far I've generated manually a calendar table with each
record containing one day.

What I'd like to do is to generate this table
automatically by providing a start date and end date and
then have a query/macro/module create the records with the
dates in between.

Example: 12/1/2003 start, 12/5/2003 end, records to be
created would be:
12/1/2003
12/2/2003
12/3/2003
12/4/2003
12/5/2003

I'd appreciate any advise how to go about creating the new
records that are between two dates.

jkochchs
 
Let me start by saying that this is generally considered a slightly
suspicious technique. There are a bunch of problems with empty data, and so
forth, but I'll assume that you've thought it through and it makes sense for
your application.

That said (whew! <eg>), you'll need to use a module. Something like:

dim rs as ADO.recordset
Set rs = currentDb("<table or query name>")

dim numDays as long
numDays = DateDiff("d", <start>, <end>)

dim x as integer
For x = 1 to numDays
With rs
.addNew
.<DateField> = DateAdd("d",x, <start>)
.Update
End with
Next x

But be aware, that's air code, so you'll probably have to fiddle with it a
bit.

HTH
--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...
 
Back
Top