Detecting Conflict Day and Time

[lotus18]

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:

   Monday(Day)   7:00 AM(TimeIN)   8:00 AM(TimeOut)   AVR(Rooom)
 

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).

Public Function Conflict(ByRef adConnection As ADODB.Connection, ByRef adRecordset _
As ADODB.Recordset, ByRef Day As String, ByRef Room As _
String, ByRef Course As String, ByRef TimeStarted As Date, ByRef TimeFinished As _
Date) As Boolean 'ByRef Subject As String,
    Conflict = False 'Default value
 
    'Opens and checks the connection
    SetConnection
    CheckConnection adRecordset
 
    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
 
    'Checks if there is duplicate title
    If adRecordset.RecordCount > 0 Then
        Conflict = True
    Else
        Conflict = False
    End If
End Function
 

Any solution will be greatly appreciated : )

Rey Sean

[9815402440]

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

[lotus18]

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

[lotus18]

Hello

Is there anyone can help me???

[CyberSoftHari]

Better you post it in Sql Server forum. There you can get it.

[lotus18]

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 :(

[Mohan Krishna]

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?

[lotus18]

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

[Mohan Krishna]

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...

[lotus18]

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

[Mohan Krishna]

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?

[lotus18]

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'.

[jaymarvinlloren]

try to change it to = instead of LIKE. It is diff. between LIKE and =.

[CyberSoftHari]

Just try Sql query

Dim strSql as String
for i = 0 to UBound(Split(Day,"/"))
<Asigen strDayStringHere by spliting Day by "/"
 
 strSql = "Select * from tblTableName WHERE Day like %'" strDayStringHere "'% and <other criteria>"
...
...
 
 

Note: 1. Day maybe a data type so use ShedDay
2. If record count is > 0 then days are repeated.

[CyberSoftHari]

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>?