Add two dates (not ranges) to the Wide Calendar with Switchboard

[mmmendoz]

Good afternoon and thank you to all the contributors of this site. I am very new and this site is a tremendous help and resource.

I am trying to add another Date field to the Wide Calendar with Switchboard database without success. Unfortunately, after a week of trying, I am still at the same place I started.

For each entry,I would like to have a Date and a FollowUpDate.

Any help would be greatly appreciated!

[ADezii]

This can be rather complicated depending on exactly how you want the Follow Up Date to be displayed.

1. Do you want both the Date and Follow Up Dates to be graphically displayed on the Calendar?
2. Do you want the Date to be displayed on the Calendar and the Follow Up Date displayed in the List Box?
3. etc...
4. Before we can proceed any further, I need to know exactly what you are looking for. If you are not crystal clear on this matter, time will be wasted on both ends.
5. Let me know your intent, and I'll se what I can do.

[mmmendoz]

Thank you for your quick response! I want both the date and follow up date to be graphically displayed on the Calendar. Both dates will be coming from the same row on the tblPatients table (Date and Date2).

I am not really concerned about the List Box.

Every entry will always have two dates (Date and Date2) usually two weeks apart.

I keep messing around with the code but just can't get it figured out. Very complicated.

[ADezii]

@mmmendoz
The Access Calendar was designed to display either a Single Date or all Dates within a Date Range, but not two independent Dates. Let me get my thinking cap on and see if I can modify the Code to suit your needs. Be patient and I'll get back to you.

[mmmendoz]

Thank you. I am still tinkering with the code. I have not figured it out by I am learning a lot in the process.

[ADezii]

1. Given the fact that you are not concerned with the List Box, I disabled all Code and References to it.
2. Change the name of the [Date] Field in tblPatients to [Date1] in order to avoid any conflicts with the Date() Function.
3. Add a [Date2] {DATE/TIME} Field to tblPatients.
4. Create the following Table Validation Rule to insure that [Date2] is always greater than [Date1]
   

   [Date2] > [Date1]

5. Both [Date1] and [Date2] must be 'Required'.
6. Replace all References to the [Date] Field in the PopulateCalendar() Sub-Routine to [Date1].
7. Add the [Date2] to strSQL in the PopulateCalendar() Sub-Routine and include additional Logic to incorporate it (Code Lines 2 and 4).
   

   strSQL = "SELECT tblPatients.Last, tblPatients.First, tblPatients.Date1, tblVisitType.Type, tblVisitType.Code, tblPatients.Time, " & _
            "tblPatients.[Date2] FROM tblVisitType INNER JOIN tblPatients ON tblVisitType.TypeID = tblPatients.TypeID " & _
            "WHERE tblPatients.Date1 Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " OR tblPatients.Date2 Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " ORDER BY tblPatients.Time, tblPatients.Last, tblPatients.First;"

8. Change the End Date qualifier in PopulateCalendar() to [Date2] (Code Line 7).
   

   Do While Not rstEvents.EOF
     'CFB added 2-18-10
     lngFirstDateInRange = rstEvents![Date1]      '<Substitute for [Start Date]>
     If lngFirstDateInRange < lngFirstOfMonth Then
       lngFirstDateInRange = lngFirstOfMonth
     End If
     lngLastDateInRange = rstEvents![Date2]         '<Substitute for [End Date]>
     If lngLastDateInRange > lngLastOfMonth Then
       lngLastDateInRange = lngLastOfMonth
     End If
    
     For lngEachDateInRange = lngFirstDateInRange To lngLastDateInRange
       If lngEachDateInRange = rstEvents![Date1] Or lngEachDateInRange = rstEvents![Date2] Then
         bytEventDayOfMonth = (lngEachDateInRange - lngLastOfPreviousMonth)
         bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
                                                 '<Substitute for [Title]>
           If astrCalendarBlocks(bytBlockCounter) = "" Then
             astrCalendarBlocks(bytBlockCounter) = Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![Last] & ", " & _
                                                   Left$(rstEvents![First], 1) & "." & " [" & rstEvents![CodeField] & "]"
           Else                                    '<Substitute for [Title]>
             astrCalendarBlocks(bytBlockCounter) = astrCalendarBlocks(bytBlockCounter) & vbNewLine & _
                                                   Format$(rstEvents![Time], "hh:nn AM/PM") & vbCrLf & rstEvents![Last] & ", " & _
                                                   Left$(rstEvents![First], 1) & "." & " [" & rstEvents![CodeField] & "]"
           End If
       End If
     Next lngEachDateInRange
     'End of CFB added 2-18-10
    
       rstEvents.MoveNext
   Loop

9. Now that we have a Date Range in a Linear Format, we only need to use the Start ([Date1]) and End Dates ([Date2]) - Code Line 13 in above listing.
10. [Date2] will also include the Time Value associated with the scheduled [Date1] Appointment. This can easily be changed later.
11. Realizing that this is a whole lot to absorb, I modified the original Data, made the required Code changes, and included as an Attachment the Database I used for this Project. Open the Calendar from the Main Menu and see that both the Start ([Date1]) and End Dates ([Date2]) are now included.
12. I am sure there will be other questions, so feel free to ask. The only request I make is that you study the Code beforehand.

[mmmendoz]

Thank you. I will study the code and incorporate this calendar with my database. I really appreciate your work!

[ADezii]

You are quite welcome. There is probably a 'cleaner' method of accomplishing what you want since you are literally creating a Range from the two Dates, processing this entire Range, but only extracting the first ([Date1]) and last ([Date2]) Dates. When I have some time I will look into this further.