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

Validating a Booking between Date/Times

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

Expand|Select|Wrap|Line Numbers
  1. BookStartDate    BookLocation    BookTime_    BookEndTime_    BookID
  2. 06/11/2011    Room    10:30 AM    01:30 PM    1
  3. 06/11/2011    Room 2    01:30 PM    06:30 PM    2
  4. 08/11/2011    Room 2    11:00 AM    05:30 PM    3
  5. 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???

Expand|Select|Wrap|Line Numbers
  1. StrSQL = "([BookStartDate] = " & Format(dteDate, "\#mm\/dd\/yyyy\#") & ") AND " & _
  2.                  "([BookLocation] = '" & strRoom & "') AND " & _
  3.                  "([BookTime] < #" & Format(dteEndTime, "Hh:Nn:Ss AM/PM") & "#) AND " & _
  4.                  "([BookEndTime] > #" & Format(dteStartTime, "Hh:Nn:Ss AM/PM") & "#) AND " & "([BookID] <> " & BookRef & ")"
  5.  
  6. If Nz(DCount("*", "qryBookings", StrSQL), 0) <> 0 Then
  7.             MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed")
  8.             Me.Undo
  9.             Exit Sub
  10. Else
  11. MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted")
  12.  
  13. DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
  14. GoTo Delete_Old_Appointment
  15. End If
  16.  
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?
Attached Files
File Type: zip Database21mdbCopy.zip (34.7 KB, 62 views)
Nov 23 '11 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [BookID]
  2.      , [BookLocation]
  3.      , [BookStartDate]
  4.      , Format([BookTime],'Medium Time') AS [BookTime_]
  5.      , Format([BookEndTime],'Medium Time') AS [BookEndTime_]
  6. 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) :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_BeforeUpdate(Cancel As Integer)
  5.     Dim strSQL As String, strMsg As String
  6.  
  7.     With Me
  8.         strSQL = "([BookLocation] = '" & .BookLocation & "') AND " & _
  9.                  "([BookStartDate] = #" & Format(.BookStartDate, _
  10.                                                 "m/d/yyyy") & "#) AND " & _
  11.                  "([BookTime] < #" & Format(.BookEndTime, _
  12.                                             "HH:nn:ss") & "#) AND " & _
  13.                  "([BookEndTime] > #" & Format(.BookTime, _
  14.                                             "HH:nn:ss") & "#)"
  15.         If Not .NewRecord Then _
  16.             strSQL = strSQL & " AND ([BookID] <> " & .BookID & ")"
  17.  
  18.         If DCount("*", "[tblRoomsBooking]", strSQL) > 0 Then
  19.             strMsg = "Your Change Conflicts with a Prior Booking, " & _
  20.                      "It Cannot be Completed"
  21.             Cancel = True
  22.         Else
  23.             strMsg = "Your Change is Valid and Will be Saved.  " & _
  24.                      "The Old Outlook Appointment will Now be Deleted"
  25.         End If
  26.         Call MsgBox(strMsg, vbInformation)
  27.     End With
  28. 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.

Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,421
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.
Nov 23 '11 #2

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


Expand|Select|Wrap|Line Numbers
  1. BookStartDate    BookLocation    BookTime_    BookEndTime_    BookID
  2. 06/11/2011    Room 1    10:30 AM    01:30 PM    1
  3. 06/11/2011    Room 2    09:00 AM    06:30 PM    2
  4. 06/11/2011    Room 3    08:00 AM    08:30 PM    3
  5. 06/11/2011    Room 4    02:30 PM    05:00 PM    4
  6.  
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:-

Expand|Select|Wrap|Line Numbers
  1. BookStartDate    BookLocation    BookTime_    BookEndTime_    BookID
  2. 06/11/2011    Room 2    10:30 AM    01:30 PM    1
  3. 06/11/2011    Room 2    09:00 AM    06:30 PM    2
  4. 06/11/2011    Room 2    08:00 AM    08:30 PM    3
  5. 06/11/2011    Room 2    02:30 PM    05:00 PM    4
  6.  
The results are surely not right?
Nov 23 '11 #3

Rabbit
Expert Mod 10K+
P: 12,421
What's the code look like now?
Nov 24 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 24 '11 #5

100+
P: 101
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?
Nov 24 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 24 '11 #7

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [BookID]
  2.      , [BookLocation]
  3.      , [BookStartDate]
  4.      , Format([BookTime],'Medium Time') AS [BookTime_]
  5.      , Format([BookEndTime],'Medium Time') AS [BookEndTime_]
  6. 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) :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_BeforeUpdate(Cancel As Integer)
  5.     Dim strSQL As String, strMsg As String
  6.  
  7.     With Me
  8.         strSQL = "([BookLocation] = '" & .BookLocation & "') AND " & _
  9.                  "([BookStartDate] = #" & Format(.BookStartDate, _
  10.                                                 "m/d/yyyy") & "#) AND " & _
  11.                  "([BookTime] < #" & Format(.BookEndTime, _
  12.                                             "HH:nn:ss") & "#) AND " & _
  13.                  "([BookEndTime] > #" & Format(.BookTime, _
  14.                                             "HH:nn:ss") & "#)"
  15.         If Not .NewRecord Then _
  16.             strSQL = strSQL & " AND ([BookID] <> " & .BookID & ")"
  17.  
  18.         If DCount("*", "[tblRoomsBooking]", strSQL) > 0 Then
  19.             strMsg = "Your Change Conflicts with a Prior Booking, " & _
  20.                      "It Cannot be Completed"
  21.             Cancel = True
  22.         Else
  23.             strMsg = "Your Change is Valid and Will be Saved.  " & _
  24.                      "The Old Outlook Appointment will Now be Deleted"
  25.         End If
  26.         Call MsgBox(strMsg, vbInformation)
  27.     End With
  28. 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.
Attached Files
File Type: zip CydEx1.Zip (27.6 KB, 48 views)
Nov 24 '11 #8

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

Expand|Select|Wrap|Line Numbers
  1. BookID    BookName    BookTime    BookStartDate    BookNotes    BookLocation    AddedToOutlook    Faculty    BookEndTime    TimeDiference    TImeConversion    myID    StartNo    EndNo
  2. 81    Cam    11:00 AM    23/11/2011    jjjjjj    Room 1    No    kkk    02:00 PM    0.125    180        660    840
  3. 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
Expand|Select|Wrap|Line Numbers
  1. trSQL = "(" & Format(dteDate, "Short Date") = [BookStartDate] & ") AND " & _
  2.                  "('" & strRoomNo & "'= [BookLocation]) AND " & _
  3.                  "('" & dteEndTime & "'<= [StartNo]) AND " & _
  4.                  "('" & dteStartTime & "'>= [EndNo])"
  5. Valid = (DCount("*", "qryEditBookings", StrSQL))
  6.  
  7.  
  8.          If Valid > 0 Then
  9.          MsgBox ("Your Change Confilcts with a Prior Prior Booking, this cannot be completed " & Valid)
  10.          Me.Undo
  11.          Exit Sub
  12.  
  13.          Else
  14.          MsgBox ("Change is Valid and saved into Database, The old Oulook Appointment will now be deleted " & Valid)
  15.  
  16.          DoCmd.RunCommand acCmdSaveRecord 'Ensure the changed booking is saved
  17.          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
Expand|Select|Wrap|Line Numbers
  1.  BookID    BookName    BookTime    BookStartDate    BookNotes    BookLocation    AddedToOutlook    Faculty    BookEndTime    TimeDiference    TImeConversion    myID    StartNo    EndNo
  2. 81    Cam    11:00 AM    23/11/2011    jjjjjj    Room 1    No    kkk    03:00 PM    0.166666666666667    240        660    900
  3. 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?
Nov 24 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 24 '11 #10

100+
P: 101
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.
Nov 24 '11 #11

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 24 '11 #12

Post your reply

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