Calendar in an Access Report that resembles Outlook

[Jesse]

I need to create a calendar report that shows the lenght of our
projects. Using Duane Hookoms calendars as a base, I was able to
output the report. However, if the lenght of a project goes beyond the
current week where it starts, I get into trouble because I'm not able
to continue the run of the project until it ends. How can I show the
whole lenght of the project. Below is a small deptiction of what I
want to show in the report.

1 2 3 4 5 6 7
X---------------------------
8 9 10 11 12 13 14
-----------------X

Thanks
Jesse

 

[Duane Hookom]

Which calendar report? Can you provide a little more information?

 

[Jesse]

I looked at the doctors weekly appointment and the boat schedule to
create something of what I want. Given two dates, I want to show all
the projects that fall within said dates. However, our projects may go
for several days or weeks. In Outlook, you could see that if the
lenght of the projects goes beyond the week end if the lenght of the
project needs it. I want to be able to recreate that. So far, I can
show the projects within a week but if the project runs beyond the week
end I don't know of to make it appear the following week. I'll provide
an example.

GET-99-990
Start: 2006-03-06
Ends: 2006-03-08
GET-99-991
Start: 2006-03-09
Ends: 206-03-15

Calendar View:

5 6 7 8 9 10 11
X---------X X--------------
12 13 14 15 16 17 18
----------------X

I'm able to recreate the 5-11 week but I' not able to show the project
to run to the next week. Thanks.

 

[Duane Hookom]

You may need to use a union query if your start and end dates are in
different weeks. This would work if the project doesn't span more than two
weeks.

SELECT Project,
DateAdd("d",-Weekday([StartDate])+1,[startDate]) AS WeekOf
FROM tblProjects
UNION
SELECT Project,
DateAdd("d",-Weekday([EndDate])+1,[EndDate])
FROM tblProjects;

A more robust solution would involve a table of all possible weeks/dates so
that you could accommodate projects spanning more than 2 weeks.

 

[Jesse]

I'll try the union query and let you know. Thanks.