Calendar--showing items between start/end date

[evansc]

I have a calendar report that seems to work fairly well. The main report is the calendar itself, with the dates/days, etc.

To show the courses on each day I have inserted a subreport. It works fine, except that courses are inserted based on their start date, and sometimes we have two or three day seminars instead of one-day seminars. In the table, all courses have a start date. Only those that last more than one day have an end date. I would like to somehow run code telling the report that if there is an end date to show the course on every day from the start date to the end date.

I assume this would need to be on the "OnOpen" even of the Report, but I don't know code enough to generate it myself, so I'm hoping someone has some ideas!

 

[dhookom]

How is the report generated? What does the record source look like?

I will take a WAG that you could create a table of all possible dates (tblDates) with a single date field (dt). Add this table to your report's record source and set the criteria under the dt field to:
Between [StartDate] AND Nz([EndDate], [StartDate])
This will create 3 records in your datasheet for a course that runs three days.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[evansc]

Hmm--when you say "all available dates" do you really mean that? Is there an easy way to fill a table with, say 20 years of dates? (Trying to be thorough.) What you say makes sense though--I'm going to experiment with it.

 

[dhookom]

Yes, I mean all potential dates for your system. I would write code that adds the records. If you don't know coding, then open Excel and copy down all the dates and then copy/paste them into your Access table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[evansc]

Let me show you my SQL--it is so close to working with what you said, that I know it must be almost right!

I have a table called Nos that list the numbers 1 through 42. The following query called "qryCreateCalendar" (which someone else gave to me) is what fills each monthly calendar.

PARAMETERS [Enter first date of month] DateTime;
SELECT IIf(Month([Enter first date of month]+[Nos]-Weekday([Enter first date of month]))=Month([Enter first date of month]),[Enter first date of month]+[Nos]-Weekday([Enter first date of month]),Null) AS CalendarDay, Nos.Nos
FROM Nos;

This query is was what I used for the subreport that listed the courses. I added your suggestion and it works alone but when I try to run the report it just keeps asking for the first day of the month.

SELECT Courses.CrsTitle, Courses.Invitees, Courses.Location, qryCreateCalendar.CalendarDay, Courses.StartDate, Courses.EndDate, Invitees.InviteesAbbr
FROM Invitees RIGHT JOIN (qryCreateCalendar LEFT JOIN Courses ON qryCreateCalendar.CalendarDay = Courses.StartDate) ON Invitees.Invitees = Courses.Invitees
WHERE (((qryCreateCalendar.CalendarDay) Between [StartDate] And Nz([EndDate],[StartDate])));

Do you have any idea what I should adjust? Thanks so much for the help!

 

[dhookom]

You should abandon all parameter queries. Replace your [Enter first date of month] with a reference to a control on a form.
Forms!frmSelect!txtFirstDate

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[evansc]

it's beautiful--thank you so much!!

 

[evansc]

I spoke too soon--now the courses only show up on the start date again. Any other ideas?

 

[dhookom]

Could you share your SQL? I don't know where you came up with "qryCreateCalendar".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[evansc]

Sure--I have a table called Nos that lists numbers 1-42.
I have an unbound form (frmCalDate) with a calendar control (CalDate) so the user can select the start of the month they wish to see.

Then following is qryCreateCalendar:

PARAMETERS [Forms]![frmCalDate]![CalDate] DateTime;
SELECT IIf(Month(Forms!frmCalDate!CalDate+[Nos]-Weekday(Forms!frmCalDate!CalDate))=Month(Forms!frmCalDate!CalDate),Forms!frmCalDate!CalDate+[Nos]-Weekday(Forms!frmCalDate!CalDate),Null) AS CalendarDay, Nos.Nos
FROM Nos;

Then following is qryFillCalendar:

SELECT qryCreateCalendar.CalendarDay, Courses.CrsTitle, Courses.Invitees, Courses.Location, Invitees.InviteesAbbr
FROM Invitees RIGHT JOIN (qryCreateCalendar LEFT JOIN Courses ON qryCreateCalendar.CalendarDay=Courses.StartDate) ON Invitees.Invitees=Courses.Invitees
WHERE (((qryCreateCalendar.CalendarDay) Between [StartDate] And Nz([EndDate],[StartDate])));

The reason I did it with the "Nos" table was so when I make the report, the dates line up under the correct day of the week and have empty spots before and after the month--like a real calendar.

 

[dhookom]

Try get rid of the join line between qryCreateCalendar.CalendarDay and Courses.StartDate.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[evansc]

Okay--did that--the funniest thing happens.

All the courses are there and correct with start/end and all between. But this one course which occurs in June 2004 for 2 days is appearing on every day of every month of all the years in the DB!

New sql:
SELECT qryCreateCalendar.CalendarDay, Courses.CrsTitle, Courses.Invitees, Courses.Location
FROM qryCreateCalendar, Courses
WHERE (((qryCreateCalendar.CalendarDay) Between [StartDate] And Nz([EndDate],[StartDate])));


Sorry to be so much trouble--but I just don't see why it's doing this!

 

[dhookom]

Check the start and end dates. Maybe subtract one from the other to see if your answer makes sense.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[evansc]

Ah--we have four courses by the same name, different years. One was entered wrong. Thanks for your help! It all works perfectly now. yeah!