Get a list of months spanning 2 dates.

[George Nicholson]

Given: a record with a TaskStart date (Feb 1 2005) and a TaskEnd date (Jan
31, 2006) and a "Forecast" of 24 hours. At it's simplest, that could mean 2
hours a month for 12 months (or some permutation thereof - the math involved
isn't the issue).

Next step: lots more records, each representing one task. All have different
start/end dates (some span 1 month, 2 months, etc.). All have different
Forecast hours. Output: Roll them up and provide a Forecast total for each
distinct month.

So, per task, how do I get a "list" of months that span Start and End? I'm
not asking about calculating DateDiff("m", Date1, Date2) (result of 12). I
need to create a list of those 12 months: Feb 2005, March 2005,... January
2006. Or I'm pretty sure I do... That's the part that has me flummoxed at
the moment. Maybe I'm simply having a brain fart (but if that's the case,
this has been an awfully long fart...)

....and then I need to cross-tab it (pretty sure, at least)

....and then I need to graph it (definitely)

But, first things first.

The only approach I'm coming up myself is writing code to do this
"manually", but I can't help thinking there is a another (better?) approach.

Any insights, thoughts or inspirational gems are welcome.
(Pokes with sharp sticks would almost be welcome at this point)

TIA,

 

[Tom Ellison]

Dear George:

Create a table containing a row for every month/year combination in the
range you may need. Probably you can just put in the dates of the first day
in each month. Make a cross-product of this in the query and filter it down
to the months between the start and end dates.

Tom Ellison