Meeting schedule with Date Range (1 Viewer)

[libin04]

Dear Experts
Can someone help me with this modification?
I have attached database for meeting schedule which i can schedule the meeting date by date.
i would required to schedule the meeting with date range.
Can you some one help me with this solution.

 

[arnelgp]

let's wait for moke...
meantime, have a look at a holiday scheduler.
Holiday Booking Database | Access World Forums (access-programmers.co.uk)

 

[MajP]

I believe this logic works
Create a new field dteDateTo for your range

tblEvents tblEvents

dteDate	dteDateTo	tmeTime	totime	EventDescription
1/10/2024​	1/12/2024​	9:00:00 AM​	10:00:00 AM​	Range 10 -12
1/12/2024​	1/15/2024​	10:10:00 AM​	11:40:00 AM​	Range 12 - 15
1/14/2024​	1/21/2024​	12:05:00 AM​	12:05:00 AM​	Range 14-21
1/12/2024​	1/12/2024​	1:05:00 PM​	3:05:00 PM​	Range 1/12
1/27/2024​	2/3/2024​	12:00:00 AM​	12:00:00 PM​	Overlap range 1/27 - 2/3

Now you will not only have to loop the dates in the calendar range, but add a sub loop to loop between From and To dates

Public Sub fGetArray()

   ' On Error GoTo fGetArray_Error

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim CurrentDate As Date
    Dim DateTo As Date
    Dim strsql As String, EndDate As Date, strKey As Variant
    Dim strItem As String, i As Integer, ctlK As Variant
    Dim strEndDate As String
    Dim strStartDate As String
   
    Set dict = New Scripting.Dictionary
   
    EndDate = DateAdd("d", 42, FirstDateOnGrid)
   
    strEndDate = "#" & Format(EndDate, "mm/dd/yyyy") & "#"
    strStartDate = "#" & Format(FirstDateOnGrid, "mm/dd/yyyy") & "#"
   
    strsql = "select dteDate, dteDateTo from  tblEvents where dteDate BETWEEN " & strStartDate & " AND " & strEndDate & " OR "
    strsql = strsql & "dteDateTo BETWEEN " & strStartDate & " AND " & strEndDate & " ORDER BY dteDate, dteDateTo DESC"
    Debug.Print strsql
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strsql)

    If rs.BOF And rs.EOF Then
        GoTo MyExit
    End If
   
    Do Until rs.EOF
        If CurrentDate < rs!dteDate Then
          CurrentDate = rs!dteDate
          DateTo = Nz(rs!dteDateTo, CurrentDate)
          If DateTo > EndDate Then DateTo = EndDate
          ' loop from current to date to
          Do
            strKey = CurrentDate
            'need to see if additional dates in range
            strItem = DateArry(CurrentDate)
            Debug.Print strItem & " cd " & CurrentDate
            dict.Add strKey, CStr(strItem)
            CurrentDate = CurrentDate + 1
          Loop Until CurrentDate > DateTo
        End If
        rs.MoveNext
    Loop

Then you have to modify the DateArry function

Function DateArry(dte As Date) As String

'   On Error GoTo DateArry_Error

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strsql As String, strOut As String
    Dim sqlDate As String
   
    sqlDate = "#" & Format(dte, "mm/dd/yyyy") & "#"
   
    strsql = "select * from  tblEvents where dteDate <= " & sqlDate & " AND dteDateTo >= " & sqlDate & " order by tmeTime"

Seems correct