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

Detecting Conflict Day and Time

lotus18
100+
P: 866
Hello World

I have a problem in detecting the conflict schedule (Day and Time).

Day
1. M
2. T
3. W
4. TH
5. F
6. S
7. M/TH <---My problem :(
8. T/F <---My problem :(
9. W/S <---My problem :(

Where: (M='Monday', [T]='Tuesday', and so on...)

Let's say I have this existing schedule on my database:

Expand|Select|Wrap|Line Numbers
  1.    Monday(Day)   7:00 AM(TimeIN)   8:00 AM(TimeOut)   AVR(Rooom)
  2.  
Here's my sample code and it is working but if the Day='M/TH' or Day='T/F' or Day='T/F' it can save the record even if it has already a schedule for that (refer to sample above).

Expand|Select|Wrap|Line Numbers
  1. Public Function Conflict(ByRef adConnection As ADODB.Connection, ByRef adRecordset _
  2. As ADODB.Recordset, ByRef Day As String, ByRef Room As _
  3. String, ByRef Course As String, ByRef TimeStarted As Date, ByRef TimeFinished As _
  4. Date) As Boolean 'ByRef Subject As String,
  5.     Conflict = False 'Default value
  6.  
  7.     'Opens and checks the connection
  8.     SetConnection
  9.     CheckConnection adRecordset
  10.  
  11.     adRecordset.Open "Select * From LoadSchedules Where " & _
  12.     "Day='" & Day & "' And Rooms.Title='" & Room & "' And Courses.Title='" & _
  13.     Course & "' And (TimeStarted Between #" & TimeStarted & "# And #" & _
  14.     TimeFinished & "# Or TimeFinished Between #" & TimeStarted & "# And #" & _
  15.     TimeFinished & "#)", dbConnection, 1, 1
  16.  
  17.     'Checks if there is duplicate title
  18.     If adRecordset.RecordCount > 0 Then
  19.         Conflict = True
  20.     Else
  21.         Conflict = False
  22.     End If
  23. End Function
  24.  
Any solution will be greatly appreciated : )

Rey Sean
Dec 2 '07 #1
Share this Question
Share on Google+
14 Replies


9815402440
100+
P: 180
hi
replace 'or' in the bold line with 'and'
adRecordset.Open "Select * From LoadSchedules Where " & _
"Day='" & Day & "' And Rooms.Title='" & Room & "' And Courses.Title='" & _
Course & "' And (TimeStarted Between #" & TimeStarted & "# And #" & _
TimeFinished & "# Or TimeFinished Between #" & TimeStarted & "# And #" & _
TimeFinished & "#)", dbConnection, 1, 1

regards
manpreet singh dhillon hoshiarpur
Dec 3 '07 #2

lotus18
100+
P: 866
hi
replace 'or' in the bold line with 'and'
adRecordset.Open "Select * From LoadSchedules Where " & _
"Day='" & Day & "' And Rooms.Title='" & Room & "' And Courses.Title='" & _
Course & "' And (TimeStarted Between #" & TimeStarted & "# And #" & _
TimeFinished & "# Or TimeFinished Between #" & TimeStarted & "# And #" & _
TimeFinished & "#)", dbConnection, 1, 1

regards
manpreet singh dhillon hoshiarpur
Hi

This query is working well, if you replace OR with AND it cannot detect conflict. What am I asking is that how can I detect conflict

Note:
'M/TH' means Monday or Thursday... they contain same schedule. It should not add a new schedule for Monday or Thursday.

Like what I said, if there is existing Day='M', then when I add a new schedule
Day='M/TH' it should prompt conflict (Assuming they have both the same room, course, and time)

Rey Sean
Dec 3 '07 #3

lotus18
100+
P: 866
Hello

Is there anyone can help me???
Dec 3 '07 #4

Expert 100+
P: 487
Better you post it in Sql Server forum. There you can get it.
Dec 3 '07 #5

lotus18
100+
P: 866
Better you post it in Sql Server forum. There you can get it.
Hello

I'm calling all the experts that could help me

Please help :(
Dec 4 '07 #6

Mohan Krishna
100+
P: 115
Hi Lotus

If I got ur problem.....
Can't u change the database field width? To store 4 characters "M/Th"...

R u storing in the same way already?
Dec 4 '07 #7

lotus18
100+
P: 866
Hi Lotus

If I got ur problem.....
Can't u change the database field width? To store 4 characters "M/Th"...

R u storing in the same way already?
Hi Mohan

Thanks for responding. All I thought that no one could help me out right here :(

Here's my database structure: click

I posted it on sql forum but unfortunately no one wants to help me :(


Rey Sean
Dec 4 '07 #8

Mohan Krishna
100+
P: 115
Hi Mohan

Thanks for responding
:
:
I posted it on sql forum but unfortunately no one wants to help me :(
Rey Sean
Hi Lotus
Here what I mean to say is..... r u storing M in one record and in a conflict record M/Th or otherwise u want to get M schedule from one record and Th schedule from other?
So, as I am in a bit confusion...
Dec 4 '07 #9

lotus18
100+
P: 866
Hi Lotus
Here what I mean to say is..... r u storing M in one record and in a conflict record M/Th or otherwise u want to get M schedule from one record and Th schedule from other?
So, as I am in a bit confusion...
Hi Mohan

I have these records on my Day Table
  1. M
  2. T
  3. W
  4. TH
  5. F
  6. S
  7. M/TH
  8. T/F
  9. W/S

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 = M/TH
  • 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: M/TH means 'Monday' or '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 : (

Rey Sean
Dec 4 '07 #10

Mohan Krishna
100+
P: 115
  1. M
  2. T
  3. W
  4. TH
  5. F
  6. S
  7. M/TH
  8. T/F
  9. W/S

Note: M/TH means 'Monday' or '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 : (
Rey Sean
Hi Lotus
Can't u put validation when u r entering/adding a new schedule on
- Day and Time
- and, if required, Room and Course

Can't u use InStr( ) for getting the M or Th?
Am I getting ur problem?
Dec 4 '07 #11

lotus18
100+
P: 866
Hi Mohan

I'm planning to change the T to TU as Tuesday so that if I used LIKE operator in sql statement it cannot select the records of TH or M/TH. But there's one problem, if I already have a schedule on 'M' and I want to add another same schedule that is 'M/TH'.
Dec 4 '07 #12

P: 1
try to change it to = instead of LIKE. It is diff. between LIKE and =.
Nov 9 '13 #13

Expert 100+
P: 487
Just try Sql query
Expand|Select|Wrap|Line Numbers
  1. Dim strSql as String
  2. for i = 0 to UBound(Split(Day,"/"))
  3. <Asigen strDayStringHere by spliting Day by "/"
  4.  
  5.  strSql = "Select * from tblTableName WHERE Day like %'" strDayStringHere "'% and <other criteria>"
  6. ...
  7. ...
  8.  
  9.  
Note: 1. Day maybe a data type so use ShedDay
2. If record count is > 0 then days are repeated.
Nov 11 '13 #14

Expert 100+
P: 487
if I already have a schedule on 'M' and I want to add another same schedule that is 'M/TH'...
plan your procedure first.
M - have some work.
M/TH - Have <another work> or <same work>?
Nov 11 '13 #15

Post your reply

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