Date grouping problem

  • Thread starter Thread starter DevourU
  • Start date Start date
D

DevourU

I am on Day 2 with this and I am about to start banging my head on the wall.
I have tasks that have a start date and an end date.
I am trying to have a report group tasks to do during a week.

Task 1 starts in week 35, ends in week 38.
Task 2 starts in week 36, ends in week 37.
Task 3 starts in week 37, ends in week 40.

Week 35
Task 1

Week 36
Task 1, Task 2

Week 37
Task 1, Task 2, Task 3

Week 38
Task 1, Task 3

Week 39
Task 3.

Clear as mud? :)

Thankx for any help ya'll can send my way.

-JS
 
Try this

create a query that has a date range of the first week.
select task where mindate >= start_date and maxdate <= end_date

print those values and use the same query but increase the start/end
date range by 7 days
loop through this as many times as you define
 
In a query, I changed date values to Week: DatePart("ww",[startdate]),
StartWeek: DatePart("ww",[startdate]), EndWeek: DatePart("ww",[enddate])

I modified your thought and came up with this:

Tasks: IIf("39" Between [Sweek] And [EWeek],[Week],0)

This gives me all tasks that are active during week 39. Now, how can I get a
report to group all tasks active during each week?

-JS
 
I think that you have the hardest part done.

All you need is another qry that would feed the one you have with the
week.

Qry to get a list of weeks and sort in the order that you need
Lets say the result is 25,26,27,28,29,30. Store the row returned and
use it as input
for the qry that you came up with. In the detail section of the report
send the
week you have stored.
Then take the result(s) of the qry "Tasks: IIf("39" Between [Sweek] And
[EWeek],[Week],0)".
Loop through the the results and store the current value out to a text
variable.
Add to the text variable each time.

(EX)
do while not record_set.eof
current record =task
task_value= task_value & task & " "
recordset.movenext
loop
text1.value=task_value

In a query, I changed date values to Week: DatePart("ww",[startdate]),
StartWeek: DatePart("ww",[startdate]), EndWeek: DatePart("ww",[enddate])

I modified your thought and came up with this:

Tasks: IIf("39" Between [Sweek] And [EWeek],[Week],0)

This gives me all tasks that are active during week 39. Now, how can I get a
report to group all tasks active during each week?

-JS



kew said:
Try this

create a query that has a date range of the first week.
select task where mindate >= start_date and maxdate <= end_date

print those values and use the same query but increase the start/end
date range by 7 days
loop through this as many times as you define
Click to expand...
Click to expand...
 
Thankx for hanging in with me. I almost got it. Works fine 'till I span a
year. If task starts in week 51 and ends in week 2, then I have all weeks
showing up between 2 and 51. D'oh!

-JS


kew said:
I think that you have the hardest part done.

All you need is another qry that would feed the one you have with the
week.

Qry to get a list of weeks and sort in the order that you need
Lets say the result is 25,26,27,28,29,30. Store the row returned and
use it as input
for the qry that you came up with. In the detail section of the report
send the
week you have stored.
Then take the result(s) of the qry "Tasks: IIf("39" Between [Sweek] And
[EWeek],[Week],0)".
Loop through the the results and store the current value out to a text
variable.
Add to the text variable each time.

(EX)
do while not record_set.eof
current record =task
task_value= task_value & task & " "
recordset.movenext
loop
text1.value=task_value

In a query, I changed date values to Week: DatePart("ww",[startdate]),
StartWeek: DatePart("ww",[startdate]), EndWeek: DatePart("ww",[enddate])

I modified your thought and came up with this:

Tasks: IIf("39" Between [Sweek] And [EWeek],[Week],0)

This gives me all tasks that are active during week 39. Now, how can I
get a
report to group all tasks active during each week?

-JS



kew said:
Try this

create a query that has a date range of the first week.
select task where mindate >= start_date and maxdate <= end_date

print those values and use the same query but increase the start/end
date range by 7 days
loop through this as many times as you define
Click to expand...
Click to expand...
Click to expand...
 
I think all you need to do is a compare of the start week and end week.
If the end week is lower then the start week you'll need to use 52 as
your end week for part 1 and use week 1 as the start week of part 2 and
ten put both of them together. This would seem to work.
 
Back
Top