How to detect a conflict on schedule?

[lotus18]

Hello all

I have these records on my Day Table for my complete database table please click here

1. M
2. T
3. W
4. TH
5. F
6. S
7. MTH
8. TF
9. WS


Let say if I have this existing schedule:

* ScheduleID = 10001
* StartTime = 8:30 AM
* EndTime = 1:00 PM
* Day = M
* Room = AVR
* Course = BSN


Then If I add this new entry

* ScheduleID = 10002
* StartTime = 9:00 AM
* EndTime = 10:00 AM
* Day = MTH
* Room = AVR
* Course = BSN

This should prompt a conflict in schedule because there is already a schedule for monday, then the new entry shouldn't be added. : )

Note: MTH means 'Monday' AND 'Thursday', I used this if they have both the same schedule. Because it would become redundant if add a new schedule for monday and add another for thursday with the same day, time, room and course. Also the same as TF and WS. I can only detect conflict if it is not a combination of both Days (e.g MTH, TF, ...) I really spend a lot of time regarding this issue. Please I really need your help : (


Here are my sample codes:

This was my original code, please please click here

 
'Let say Day1='M' and Day2='TH' as we're going to used the example above
 
Public Function Conflict(ByRef adConnection As ADODB.Connection, ByRef adRecordset _
As ADODB.Recordset, ByRef Day1 As String, ByRef Day2 As String, ByRef Room As _
String, ByRef Course As String, ByRef Section As String, ByRef Semester As String, _
ByRef TimeStarted As Date, ByRef TimeFinished As Date) As Boolean
 
adRecordset.Open "Select * From LoadSchedules Where (DayA='" & Day1 & "'" & _
    " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
    "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
    "(TimeStarted <= #" & TimeStarted & "# And " & _
    "TimeFinished >= #" & TimeStarted & "#) Or (#" & TimeStarted & "#" & _
    "<= TimeStarted And TimeFinished < #" & TimeFinished & "#) Or (#" & _
    TimeStarted & "# <= TimeStarted And TimeStarted < #" & TimeFinished & "#)) Or " & _
    "(DayB='" & Day2 & "'" & _
    " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
    "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
    "(TimeStarted <= #" & TimeStarted & "# And " & _
    "TimeFinished >= #" & TimeStarted & "#) Or (#" & TimeStarted & "#" & _
    "<= TimeStarted And TimeFinished < #" & TimeFinished & "#) Or (#" & _
    TimeStarted & "# <= TimeStarted And TimeStarted < #" & TimeFinished & "#))", _
    dbConnection, 1, 1
 
'Checks if there is conflict
    If adRecordset.RecordCount > 0 Then
        Conflict = True
    Else
        Conflict = False
    End If
 
 

And I also tried Between And Operator. It is still same results above.

 
'Let say Day1='M' and Day2='TH' as we're going to used the example above
 
Public Function Conflict(ByRef adConnection As ADODB.Connection, ByRef adRecordset _
As ADODB.Recordset, ByRef Day1 As String, ByRef Day2 As String, ByRef Room As _
String, ByRef Course As String, ByRef Section As String, ByRef Semester As String, _
ByRef TimeStarted As Date, ByRef TimeFinished As Date) As Boolean
 
 adRecordset.Open "Select * From LoadSchedules Where (DayA='" & Day1 & "'" & _
    " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
    "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
    "(TimeStarted Between #" & TimeStarted & "# And #" & TimeFinished & "# Or " & _
    "TimeFinished Between #" & TimeStarted & "# And #" & TimeFinished & "#)) Or " & _
    "(DayB='" & Day2 & "'" & _
    " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
    "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
    "(TimeStarted Between #" & TimeStarted & "# And #" & TimeFinished & "# Or " & _
    "TimeFinished Between #" & TimeStarted & "# And #" & TimeFinished & "#))", _
    dbConnection, 1, 1
 
'Checks if there is conflict
    If adRecordset.RecordCount > 0 Then
        Conflict = True
    Else
        Conflict = False
    End If
 
 

Rey Sean

[lotus18]

Hello....

Could anyone help me this out?

[Killer42]

I've just got back from vacation. Since nobody else seems to be helping, I'll try to have a look at it within the next day or two. If you like though, I can move it to the Access forum, where I think you'll find much more expertise with this kind of thing.

[lotus18]

I've just got back from vacation. Since nobody else seems to be helping, I'll try to have a look at it within the next day or two. If you like though, I can move it to the Access forum, where I think you'll find much more expertise with this kind of thing.

Hi Killer42


Thanks for your reply and concern

Rey Sean

[Killer42]

Just started looking into this now. It seems as though you'd simplify things somewhat if you use a unique character for each day. For instance...

Code   Meaning
M      Monday
T      Tuesday
W      Wednesday
R      Thursday
F      Friday
S      Saturday
MR     Monday & Thursday
TF     Tuesday & Friday
WS     Wednesday & Saturday
 

That way, you can easily detect a hit on say, Thursday, by checking if the day contains "R" (Like "*R*"), regardless of what else is there.

[QVeen72]

Hi,

Change the Day Structure like as Killer Suggested..
And Change Your SQL Statement :

adRecordset.Open "Select * From LoadSchedules Where " _
  & " DayA='" & Day1 & "'" & _
    " And Rooms.Title='" & Room _
  & "' And Courses.Title='" & Course & "' And " & _
    "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
    " Day Like '*" & Day & "*' And ((" _
    & TimeStarted & " Between TimeStarted And " & _
    "TimeFinished)  Or (" & TimeFinished & " Between TimeStarted And TimeFinished)) " 
 

Regards
Veena

[lotus18]

Hi Killer42

Hi Veena

I'm planning to display all the time which has no conflicts on a combo box and from there, the user can only selects the timeIN and TimeOUT and it would never prompt a conflict . Is it possible?

Rey Sean

[lotus18]

Just started looking into this now. It seems as though you'd simplify things somewhat if you use a unique character for each day. For instance...

Code   Meaning
M      Monday
T      Tuesday
W      Wednesday
R      Thursday
F      Friday
S      Saturday
MR     Monday & Thursday
TF     Tuesday & Friday
WS     Wednesday & Saturday
 

That way, you can easily detect a hit on say, Thursday, by checking if the day contains "R" (Like "*R*"), regardless of what else is there.

Hi Killer42

I think this code applies only to a single day. How about the combination of days let us say Monday or Thursday (MR)?

For example:

If i have already a schedule on Thursday (R) and then I add a new schedule for Monday and Thursday (MR) same as records on thursday (rooms, subjects,...). How can you use *MR* to check R? This should prompt conflict.

[QVeen72]

Hi,

When You have Combination, Build the SQL Statement to Split and Check..
You need to Check for '*M*' As well As '*R*'

Though not a Very elegant Programming Practice, You can use ComboBox to give Time Selection..
How are you planning to Populate Combo...?
Is it "09:00", "09:30", "10:00"...? (Intervals of 30 mins..?)
What If the User want From "09:15"..?

Regards
Veena

[lotus18]

Interval of 30 mins. Is it possible?

Rey Sean

[QVeen72]

Hi,

Yes, it is possible,

    Combo1.Clear
    Dim i As Integer
    Dim TDate As Date
    TDate = 0
    For i = 1 To 48
        TDate = DateAdd("n", 30, TDate)
        Combo1.AddItem Format(TDate, "hh:mm")
    Next
 

Regards
Veena

[lotus18]

Hi Veena

What I really meant was, what if i have this schedules in my records for tuesday (as for example). The minimum time is 7:00AM and the maximum time is 8:30PM.

TimeIN   TimeOUT
7:00AM   8:30AM
10:00AM   1:00PM
4:00PM   7:00PM
 

Then the combobox must add this following items for TimeIN

TimeIN
8:30AM
9:00AM
9:30AM
1:00PM
1:30PM
2:00PM
2:30PM
3:00PM
3:30PM
7:00PM
7:30PM
 

Is i possible to remove the items that are conflicted? Hope you get what I really meant.

Rey Sean

[lotus18]

When You have Combination, Build the SQL Statement to Split and Check..
You need to Check for '*M*' As well As '*R*'

Hi Veena

I'm not yet familiar with Split in SQL statement. Could you please show me how to this. Thanks.

Rey Sean

[QVeen72]

Hi,

Yes, it is possible
First Open a Recordset and Populate the Array with All the Booked Times, Say FArr(From Time array) and TArr(To Time Array)

Here, I'am populating with the Data Given by you..
Check this code:

    Combo1.Clear
    Dim i As Integer
    Dim TDate As Date
    Dim AddFlag As Boolean
    Dim j As Integer
    '
    Dim FArr(0 To 2) As Date
    Dim TArr(0 To 2) As Date
    FArr(0) = "07:00Am": TArr(0) = "08:30AM"
    FArr(1) = "10:00AM": TArr(1) = "01:00PM"
    FArr(2) = "04:00PM": TArr(2) = "07:00PM"
    '
    TDate = 0
    For i = 1 To 48
        TDate = DateAdd("n", 30, TDate)
        AddFlag = True
        For j = LBound(FArr) To UBound(FArr)
            If Format(TDate, "hh:mmAM/PM") > FArr(j) And   _
                     Format(TDate, "hh:mmAM/PM") < TArr(j) Then
                AddFlag = False
                Exit For
            End If
        Next
        If AddFlag Then
            Combo1.AddItem Format(TDate, "hh:mmAM/PM")
        End If
    Next
 

Run the Code, and Refine it you may need to check for >= and <=...

REgards
Veena

[lotus18]

Hi Veena

I modified your codes:

Combo1.Clear
          Dim i As Integer
          Dim TDate As Date, MaxTime As Date 'VARIABLE ADDED
          Dim AddFlag As Boolean
          Dim j As Integer
          '
          Dim FArr(0 To 2) As Date
          Dim TArr(0 To 2) As Date
          FArr(0) = "07:00Am": TArr(0) = "08:30AM"
          FArr(1) = "10:00AM": TArr(1) = "01:00PM"
          FArr(2) = "04:00PM": TArr(2) = "07:00PM"
          '
          TDate = #7:00:00 AM#
          MaxTime = #8:00:00 PM# 'ADDED
          For i = 1 To 48
              TDate = DateAdd("n", 30, TDate)
              AddFlag = True
              For j = LBound(FArr) To UBound(FArr)
                  If Format(TDate, "hh:mmAM/PM") >= FArr(j) And _ 'MODIFIED
                           Format(TDate, "hh:mmAM/PM") < TArr(j) Then
                      AddFlag = False
                      Exit For
                  End If
              Next
 
              If AddFlag Then
                    If TDate > MaxTime Then 'ADDED
                        Combo1.AddItem Format(TDate, "hh:mmAM/PM")
                    End If
              End If
          Next

Thanks for the codes Veena. A million thanks to you. : )
Now, my problem is how to connect it with the database record. I'll try do it. Good Luck to me.

Rey Sean