Arrange report into a calendar layout in access 2003

[akum78]

Hi, I've been trying for a while now to arrange one of my reports into a calendar layout in access 2003. The current report takes the values of a query made from an intake table which contains the following columns: DateOfService, therapistName. All Im trying to do is to mege those values into a report that has a calendar layout which will only display the name of the therapist and the count of how many times the name appeared in that date. as for example: january the 4th which is monday, that square will only have Joe Smith 3 because that's the only therapist who serviced 3 individuals that day and so on.

I tried doing it my way with an example I found online but it comes up with many duplicates and goes over the 31 days rule for january. I attached a screen shot of the real report and how I am trying to lay it out.

can anyone tell me what am I doing wrong?

[ADezii]

Just subscribing. May have an idea and will return later.

[ADezii]

I've adapted an Access Calendar to your specific needs.

1. Click on a Field to expand related information below in the List Box.
2. To Print the Calendar, set the Page Orientation to Landscape, then click on the Print Button.
3. To Add more Data, populate tblTest in any manner you like.
4. Download the Attachment to see what I mean.

[akum78]

You my friend did a wonder full job, I never thought about doing it in a form style.
thank you so much

[ADezii]

You are quite welcome. It's just that I've adapted the Calendar to many specific needs over the years, and I thought that it may be useful in this scenario.

[akum78]

I've got to tell you that calendar is a beauty. I adjusted the SQL statement and it works like a charm, my only concern is that now Im trying to adjust the code to see the count on the boxes along with the information that is alredy there (therapist names). sorry for bothering again but would you please point out the line that populates the squares with the information, or a snippet that will show the count besides the respective name, as it does on the bottom, but instead of doing it there it would be on each square with only names and the count.

Thank you,

[ADezii]

In the PopulateCalenda r() Sub-Routine, modify 'Relative' Code Lines 6 and 9, a variation of which you can see below:

'...Code intentionally omitted
For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
    bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
    bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
      If astrCalendarBlocks(bytBlockCounter) = "" Then
        astrCalendarBlocks(bytBlockCounter) = rstEvents![TherapistName] & " [" & rstEvents![Total Served] & "]"
      Else                                    '<Substitute for [Title]>
        astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                              rstEvents![TherapistName] & " [" & rstEvents![Total Served] & "]"
      End If
  Next lngEachDateInRange
'...Code intentionally omitted

[akum78]

great, that's what I needed, thank you.