By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,644 Members | 2,105 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,644 IT Pros & Developers. It's quick & easy.

How to detect a conflict on schedule?

lotus18
100+
P: 866
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'Let say Day1='M' and Day2='TH' as we're going to used the example above
  3.  
  4. Public Function Conflict(ByRef adConnection As ADODB.Connection, ByRef adRecordset _
  5. As ADODB.Recordset, ByRef Day1 As String, ByRef Day2 As String, ByRef Room As _
  6. String, ByRef Course As String, ByRef Section As String, ByRef Semester As String, _
  7. ByRef TimeStarted As Date, ByRef TimeFinished As Date) As Boolean
  8.  
  9. adRecordset.Open "Select * From LoadSchedules Where (DayA='" & Day1 & "'" & _
  10.     " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
  11.     "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
  12.     "(TimeStarted <= #" & TimeStarted & "# And " & _
  13.     "TimeFinished >= #" & TimeStarted & "#) Or (#" & TimeStarted & "#" & _
  14.     "<= TimeStarted And TimeFinished < #" & TimeFinished & "#) Or (#" & _
  15.     TimeStarted & "# <= TimeStarted And TimeStarted < #" & TimeFinished & "#)) Or " & _
  16.     "(DayB='" & Day2 & "'" & _
  17.     " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
  18.     "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
  19.     "(TimeStarted <= #" & TimeStarted & "# And " & _
  20.     "TimeFinished >= #" & TimeStarted & "#) Or (#" & TimeStarted & "#" & _
  21.     "<= TimeStarted And TimeFinished < #" & TimeFinished & "#) Or (#" & _
  22.     TimeStarted & "# <= TimeStarted And TimeStarted < #" & TimeFinished & "#))", _
  23.     dbConnection, 1, 1
  24.  
  25. 'Checks if there is conflict
  26.     If adRecordset.RecordCount > 0 Then
  27.         Conflict = True
  28.     Else
  29.         Conflict = False
  30.     End If
  31.  
  32.  
And I also tried Between And Operator. It is still same results above.

Expand|Select|Wrap|Line Numbers
  1.  
  2. 'Let say Day1='M' and Day2='TH' as we're going to used the example above
  3.  
  4. Public Function Conflict(ByRef adConnection As ADODB.Connection, ByRef adRecordset _
  5. As ADODB.Recordset, ByRef Day1 As String, ByRef Day2 As String, ByRef Room As _
  6. String, ByRef Course As String, ByRef Section As String, ByRef Semester As String, _
  7. ByRef TimeStarted As Date, ByRef TimeFinished As Date) As Boolean
  8.  
  9.  adRecordset.Open "Select * From LoadSchedules Where (DayA='" & Day1 & "'" & _
  10.     " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
  11.     "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
  12.     "(TimeStarted Between #" & TimeStarted & "# And #" & TimeFinished & "# Or " & _
  13.     "TimeFinished Between #" & TimeStarted & "# And #" & TimeFinished & "#)) Or " & _
  14.     "(DayB='" & Day2 & "'" & _
  15.     " And Rooms.Title='" & Room & "' And Courses.Title='" & Course & "' And " & _
  16.     "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
  17.     "(TimeStarted Between #" & TimeStarted & "# And #" & TimeFinished & "# Or " & _
  18.     "TimeFinished Between #" & TimeStarted & "# And #" & TimeFinished & "#))", _
  19.     dbConnection, 1, 1
  20.  
  21. 'Checks if there is conflict
  22.     If adRecordset.RecordCount > 0 Then
  23.         Conflict = True
  24.     Else
  25.         Conflict = False
  26.     End If
  27.  
  28.  
Rey Sean
Dec 7 '07 #1
Share this Question
Share on Google+
14 Replies


lotus18
100+
P: 866
Hello....

Could anyone help me this out?
Dec 9 '07 #2

Expert 5K+
P: 8,434
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.
Dec 10 '07 #3

lotus18
100+
P: 866
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
Dec 10 '07 #4

Expert 5K+
P: 8,434
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...

Expand|Select|Wrap|Line Numbers
  1. Code   Meaning
  2. M      Monday
  3. T      Tuesday
  4. W      Wednesday
  5. R      Thursday
  6. F      Friday
  7. S      Saturday
  8. MR     Monday & Thursday
  9. TF     Tuesday & Friday
  10. WS     Wednesday & Saturday
  11.  
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.
Dec 12 '07 #5

QVeen72
Expert 100+
P: 1,445
Hi,

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

Expand|Select|Wrap|Line Numbers
  1. adRecordset.Open "Select * From LoadSchedules Where " _
  2.   & " DayA='" & Day1 & "'" & _
  3.     " And Rooms.Title='" & Room _
  4.   & "' And Courses.Title='" & Course & "' And " & _
  5.     "Sections.Title='" & Section & "' And Semester='" & Semester & "' And " & _
  6.     " Day Like '*" & Day & "*' And ((" _
  7.     & TimeStarted & " Between TimeStarted And " & _
  8.     "TimeFinished)  Or (" & TimeFinished & " Between TimeStarted And TimeFinished)) " 
  9.  

Regards
Veena
Dec 12 '07 #6

lotus18
100+
P: 866
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
Dec 12 '07 #7

lotus18
100+
P: 866
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...

Expand|Select|Wrap|Line Numbers
  1. Code   Meaning
  2. M      Monday
  3. T      Tuesday
  4. W      Wednesday
  5. R      Thursday
  6. F      Friday
  7. S      Saturday
  8. MR     Monday & Thursday
  9. TF     Tuesday & Friday
  10. WS     Wednesday & Saturday
  11.  
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.
Dec 12 '07 #8

QVeen72
Expert 100+
P: 1,445
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
Dec 12 '07 #9

lotus18
100+
P: 866
Interval of 30 mins. Is it possible?

Rey Sean
Dec 12 '07 #10

QVeen72
Expert 100+
P: 1,445
Hi,

Yes, it is possible,

Expand|Select|Wrap|Line Numbers
  1.     Combo1.Clear
  2.     Dim i As Integer
  3.     Dim TDate As Date
  4.     TDate = 0
  5.     For i = 1 To 48
  6.         TDate = DateAdd("n", 30, TDate)
  7.         Combo1.AddItem Format(TDate, "hh:mm")
  8.     Next
  9.  

Regards
Veena
Dec 12 '07 #11

lotus18
100+
P: 866
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.

Expand|Select|Wrap|Line Numbers
  1. TimeIN   TimeOUT
  2. 7:00AM   8:30AM
  3. 10:00AM   1:00PM
  4. 4:00PM   7:00PM
  5.  
Then the combobox must add this following items for TimeIN

Expand|Select|Wrap|Line Numbers
  1. TimeIN
  2. 8:30AM
  3. 9:00AM
  4. 9:30AM
  5. 1:00PM
  6. 1:30PM
  7. 2:00PM
  8. 2:30PM
  9. 3:00PM
  10. 3:30PM
  11. 7:00PM
  12. 7:30PM
  13.  
Is i possible to remove the items that are conflicted? Hope you get what I really meant.

Rey Sean
Dec 12 '07 #12

lotus18
100+
P: 866
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
Dec 12 '07 #13

QVeen72
Expert 100+
P: 1,445
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:
Expand|Select|Wrap|Line Numbers
  1.     Combo1.Clear
  2.     Dim i As Integer
  3.     Dim TDate As Date
  4.     Dim AddFlag As Boolean
  5.     Dim j As Integer
  6.     '
  7.     Dim FArr(0 To 2) As Date
  8.     Dim TArr(0 To 2) As Date
  9.     FArr(0) = "07:00Am": TArr(0) = "08:30AM"
  10.     FArr(1) = "10:00AM": TArr(1) = "01:00PM"
  11.     FArr(2) = "04:00PM": TArr(2) = "07:00PM"
  12.     '
  13.     TDate = 0
  14.     For i = 1 To 48
  15.         TDate = DateAdd("n", 30, TDate)
  16.         AddFlag = True
  17.         For j = LBound(FArr) To UBound(FArr)
  18.             If Format(TDate, "hh:mmAM/PM") > FArr(j) And   _
  19.                      Format(TDate, "hh:mmAM/PM") < TArr(j) Then
  20.                 AddFlag = False
  21.                 Exit For
  22.             End If
  23.         Next
  24.         If AddFlag Then
  25.             Combo1.AddItem Format(TDate, "hh:mmAM/PM")
  26.         End If
  27.     Next
  28.  

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

REgards
Veena
Dec 12 '07 #14

lotus18
100+
P: 866
Hi Veena

I modified your codes:

Expand|Select|Wrap|Line Numbers
  1. Combo1.Clear
  2.           Dim i As Integer
  3.           Dim TDate As Date, MaxTime As Date 'VARIABLE ADDED
  4.           Dim AddFlag As Boolean
  5.           Dim j As Integer
  6.           '
  7.           Dim FArr(0 To 2) As Date
  8.           Dim TArr(0 To 2) As Date
  9.           FArr(0) = "07:00Am": TArr(0) = "08:30AM"
  10.           FArr(1) = "10:00AM": TArr(1) = "01:00PM"
  11.           FArr(2) = "04:00PM": TArr(2) = "07:00PM"
  12.           '
  13.           TDate = #7:00:00 AM#
  14.           MaxTime = #8:00:00 PM# 'ADDED
  15.           For i = 1 To 48
  16.               TDate = DateAdd("n", 30, TDate)
  17.               AddFlag = True
  18.               For j = LBound(FArr) To UBound(FArr)
  19.                   If Format(TDate, "hh:mmAM/PM") >= FArr(j) And _ 'MODIFIED
  20.                            Format(TDate, "hh:mmAM/PM") < TArr(j) Then
  21.                       AddFlag = False
  22.                       Exit For
  23.                   End If
  24.               Next
  25.  
  26.               If AddFlag Then
  27.                     If TDate > MaxTime Then 'ADDED
  28.                         Combo1.AddItem Format(TDate, "hh:mmAM/PM")
  29.                     End If
  30.               End If
  31.           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
Dec 13 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.