Validating a Booking between Date/Times

[Cyd44]

I am afraid I am back to an old question which I thought I had resolved. I am now UAT testing and have found that the logic of my SQL statement does not work.

I have the following records in a table and want to edit a record and validate the change before saving it. This is the result of the qryBookings that my Dcount function evaluates the SQL against.

BookStartDate    BookLocation    BookTime_    BookEndTime_    BookID
06/11/2011    Room    10:30 AM    01:30 PM    1
06/11/2011    Room 2    01:30 PM    06:30 PM    2
08/11/2011    Room 2    11:00 AM    05:30 PM    3
10/11/2011    Room 2    02:30 PM    05:00 PM    4

However, If I change record 1 to Room 2 and keep the same date/time the Sql says it is Invalid. Also if I change Record 3 to 6/11/2011 and start time to 1:30PM it says it is Invalid. Both of these changes should be valid?

If I change Record 1 to Room 3 it validates OK?

Here is the code I am using for which I received a lot of help.....I thought it worked OK at the Time???

StrSQL = "([BookStartDate] = " & Format(dteDate, "\#mm\/dd\/yyyy\#") & ") AND " & _
                 "([BookLocation] = '" & strRoom & "') AND " & _
                 "([BookTime] < #" & Format(dteEndTime, "Hh:Nn:Ss AM/PM") & "#) AND " & _
                 "([BookEndTime] > #" & Format(dteStartTime, "Hh:Nn:Ss AM/PM") & "#) AND " & "([BookID] <> " & BookRef & ")"
 
If Nz(DCount("*", "qryBookings", StrSQL), 0) <> 0 Then
            MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed")
            Me.Undo
            Exit Sub
Else
MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted")
 
DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
GoTo Delete_Old_Appointment
End If
 

I really thought I had put this to bed but obviously not. I will enclose the small database (saved in 2003 format) in order that you can see for yourselves that the validation is not correct.

Sorry to repeat the question guys but I now realise how difficult working wuth date & time is. I feel there is perhaps an OR statement missing or something?

[Rabbit]

Both are correctly invalid.

In the first case, the end time of record 1 at 1:30 PM is not before the start time of record 2 at 1:30 PM. They overlap by anywhere between 1 millisecond to 59.999 seconds.

In the second case, record 3's new start and end time of 1:30 AM to 5:30 PM overlaps with record 2's start and end time of 1:30 PM to 6:30 PM.

The code is working as it should.

[Cyd44]

Ok Rabbit, this is driving me up the wall. I have now added <= and >= to the condition as I would like to be able to book a room when times = so End at 10Am for i booking and Start at 10Am for another.

Here is the current Table:-

BookStartDate    BookLocation    BookTime_    BookEndTime_    BookID
06/11/2011    Room 1    10:30 AM    01:30 PM    1
06/11/2011    Room 2    09:00 AM    06:30 PM    2
06/11/2011    Room 3    08:00 AM    08:30 PM    3
06/11/2011    Room 4    02:30 PM    05:00 PM    4
 

Here are my Changes and results:-
Changed Record 1 to Room 2 - system said Valid? This clearly conflicts with Record 2
Changed Record 3 to Room 2 - system said Valid agian?
Changed Record 4 to Room 2 -system said Valid.

Here is the table after changes:-

BookStartDate    BookLocation    BookTime_    BookEndTime_    BookID
06/11/2011    Room 2    10:30 AM    01:30 PM    1
06/11/2011    Room 2    09:00 AM    06:30 PM    2
06/11/2011    Room 2    08:00 AM    08:30 PM    3
06/11/2011    Room 2    02:30 PM    05:00 PM    4
 

The results are surely not right?

[Rabbit]

What's the code look like now?

[NeoPa]

Cyd, the code in the attached database seems vastly different from that posted. The posted code looks more advanced. <= and >= would appear to make sense, but if you're booking a room then it's generally acceptable to allow the end time of one to match the start time of another.

From your last post, and assuming the original posted code, but amended to use >= and <= in place of > and <, your first change being valid surprises me; The second doesn't surprise me and the third surprises me again.

After finding that the database was quite different from the reported behaviour though, I suspect this is a case of losing sight of some details somewhere and not an accurate reflection of what we think is happening. Certainly, looking at your original SQL, as displayed in post #1, I can find no fault with it. I suspect if this logic were inserted into an otherwise clean database it would work exactly as expected.

[Cyd44]

Hi NeoPa,

Thanks again. I have researched a number of algorithms for this and the one I favour most of all is the one I have posted above. I had originally thought this was OK but I then developed a test plan and had totally emptied the database of any records.

Part of my testing was to create 2 bookings (same date, same room) with a 2 hour gap between them. I then tried to add another booking to fit between the two and got a conflict message. This totally confused me and I then tried using other algorithms in order to resolve this.

I have now got myself into a pickle over this and another problem is that I am using 2010 and cannnot save in 2003 because of the incompatability and cannot provide my database for you to look at.

I will go back and empty the tables agian and start from scratch to see if there is some corruption.

I had no idea that this would be so complicated but I feel it is definitely something to do with the Time fields. I am wondering whether it might be better to convert time to an integer value first and then compare them?

[NeoPa]

Good thinking, but not likely to help you get a solution in this case I'm afraid.

What I suggest you do is to create a very basic 'proof of concept' database that you can use to convince yourself that the problem is not in the algorithm. I know the algorithm probably came from me in the first place, but I'm not stupid, and I can tell you that when going through it carefully it produces sensible results.

As I said in my earlier post, I suspect something else somewhere in your current database is causing issues that is confusing this situation.

[NeoPa]

I went to tidy up your database and found your problem. It was pretty well exactly as I'd anticipated - a detail problem elsewhere than in your logic.

[qryBookings] is essentially :

SELECT [BookID]
     , [BookLocation]
     , [BookStartDate]
     , Format([BookTime],'Medium Time') AS [BookTime_]
     , Format([BookEndTime],'Medium Time') AS [BookEndTime_]
FROM   [tblRoomsBooking]

This means your SQL WHERE clause is relying on automatic conversion as you're trying to compare Date/Time values with strings. Using Format() when processing data is almost never to your advantage. It should only ever be used at the point of display.

I rewrote your code and put it in the form's BeforeUpdate() event procedure and it worked perfectly as expected (I also stripped out the rest of the code and other controls and fields which were of no help and could only tend to confuse matters) :

Option Compare Database
Option Explicit
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strSQL As String, strMsg As String
 
    With Me
        strSQL = "([BookLocation] = '" & .BookLocation & "') AND " & _
                 "([BookStartDate] = #" & Format(.BookStartDate, _
                                                "m/d/yyyy") & "#) AND " & _
                 "([BookTime] < #" & Format(.BookEndTime, _
                                            "HH:nn:ss") & "#) AND " & _
                 "([BookEndTime] > #" & Format(.BookTime, _
                                            "HH:nn:ss") & "#)"
        If Not .NewRecord Then _
            strSQL = strSQL & " AND ([BookID] <> " & .BookID & ")"
 
        If DCount("*", "[tblRoomsBooking]", strSQL) > 0 Then
            strMsg = "Your Change Conflicts with a Prior Booking, " & _
                     "It Cannot be Completed"
            Cancel = True
        Else
            strMsg = "Your Change is Valid and Will be Saved.  " & _
                     "The Old Outlook Appointment will Now be Deleted"
        End If
        Call MsgBox(strMsg, vbInformation)
    End With
End Sub

I stopped short of redoing the form completely for time reasons, but I noticed every time it was saved it took ages (really ages). I did Compact and Repair it, which made no difference, so I would suggest you start the form again from scratch. Use the existing code, as that works as a starting point, but something is quite wrong with that form. I hope it's not the database itself, but it appears to be just the form.

[Cyd44]

OK neoPa I am working on a simple database for proof of concept and have the following table containing the two records only. You will see that I have used a long variable for StartNo and EndNo (being the BookTime and BookEndTime fields multiplied by 1440 to give a number of minutes. I substituted the StartNo and EndNo variables to see if it was the Time that was causing a problem.

BookID    BookName    BookTime    BookStartDate    BookNotes    BookLocation    AddedToOutlook    Faculty    BookEndTime    TimeDiference    TImeConversion    myID    StartNo    EndNo
81    Cam    11:00 AM    23/11/2011    jjjjjj    Room 1    No    kkk    02:00 PM    0.125    180        660    840
82    Cam    02:00 PM    23/11/2011        Room 1    No        04:30 PM    0.104166666666667    150        840    990 

I then went to change record 1 and changed the end time to 3pm in order to clash with record 2 start time. I have moved the record variables to the end of the condition rather than the start of it in order to compare form variables with record one (rather than the other way round). My code used is

trSQL = "(" & Format(dteDate, "Short Date") = [BookStartDate] & ") AND " & _
                 "('" & strRoomNo & "'= [BookLocation]) AND " & _
                 "('" & dteEndTime & "'<= [StartNo]) AND " & _
                 "('" & dteStartTime & "'>= [EndNo])"
Valid = (DCount("*", "qryEditBookings", StrSQL))
 
 
         If Valid > 0 Then
         MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed " & Valid)
         Me.Undo
         Exit Sub
 
         Else
         MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted " & Valid)
 
         DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
         End If 

Going back to my change, the system validated the booking even though it conflicted.

If we look at the algorithm, we are asking that dteStartTime >= EndNo and dteEndTime <= StartNo

In my change the StartTime (number) was 660 and the record EndNo was 990 this is invalid. Now my change for EndTime (number) was 900 and the Record StartTime (number) was 840, once agian invalid. However, we have validated, correcty, a booking which ends after another one Starts, IE End Record (1) 3PM and Start Record (2) at 2PM.

Here is the table After Edit

 BookID    BookName    BookTime    BookStartDate    BookNotes    BookLocation    AddedToOutlook    Faculty    BookEndTime    TimeDiference    TImeConversion    myID    StartNo    EndNo
81    Cam    11:00 AM    23/11/2011    jjjjjj    Room 1    No    kkk    03:00 PM    0.166666666666667    240        660    900
82    Cam    02:00 PM    23/11/2011        Room 1    No        04:30 PM    0.104166666666667    150        840    990

I am confused here as it does appear to me that we might be missing a condition to test the End of one does not conflict with the start of another. I am sorry if I appear thick here but I would have expected that the change above would have conflicted?

Interestingly, I put a MsgBox (strSQL) in my code and this should a value of False? Which does appear to be a correct evaluation but the DCount varialble returns 0?

[NeoPa]

Wow. If that's what you call simplifying things I should have said to try to complicate matters instead :-D

I suspect you missed my previous post (#8) as I sneaked it in as you were preparing #9. Have a look through that and notice how simple the solution is. Very basic. Everything done in native form (Dates and times treated as dates and times etc).

If you still have problems after that then let's discuss them, but related to that simple model rather than post #9 as I don't even want to think about what's going on there (I'm fundamentally lazy you see).

PS. You say :

Cyd44:
I am confused here as it does appear to me that we might be missing a condition to test the End of one does not conflict with the start of another. I am sorry if I appear thick here but I would have expected that the change above would have conflicted?

This is a natural question to ask, but if you study the logic carefully you'll see that actually no more is required. It's a bit like using pure maths to find the crux of the matter.

[Cyd44]

Hi NeoPa

You wer right, we had crossed messages and I had not seen your advice. Have placed the code in my database and it appears to work fine. I did get a funny but I think it was relating to the fact that I was editing and re-editing the same record each time and this resulted in some funny results (think some variables had been left behind). I have set the event to close the form after each edit now in order that we get a full refresh and I have manged to pass the test I had planned as a result.
I concur to the fact that I was overcomplicating things but this was out of frustration as I thought the Time fields were the cause of the problem.
Result however is a simple and clean piece of code. Many thanks agian my friend.

[NeoPa]

Cyd44:
I concur to the fact that I was overcomplicating things but this was out of frustration as I thought the Time fields were the cause of the problem.

That's pretty well what I'd figured Cyd :-)

I've been there myself, and seen it in others a fair bit too. It's easy to focus in the wrong places when things get confusing. I'm very pleased it all makes more sense now.