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?
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.
11 2323
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.
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?
What's the code look like now?
NeoPa 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB
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.
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 32,556
Expert Mod 16PB 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tim |
last post by:
I need to get the number of seconds when subtracting two date/times.
With the code I am using below I subtract the two date/times and end
up with a total of days like "1.56". Then when I multiply...
|
by: Astra |
last post by:
Hi All
I have a <SELECT> for the month (1 .. 12) and a <SELECT> for the year (2004
.... 2020), do you know of any js validation check I can use to check whether
these values are older than...
|
by: Steve |
last post by:
I am currently trying to validate data in an access database. I need
to verify that columns containing date information are in the format
ddmmyyyy and columns containg time information are in the...
|
by: Chris |
last post by:
Hi,
how do I validate a date using a RegularExpression-validator control ?
e.g. 05/20/2004
(May 20, 2004)
Or should I use another validator-control ? and how ?
Thanks
|
by: Becki |
last post by:
hi, i need to make a calculation that calculates the current length of stay for people in a hotel. so i need to take todays date from the day they booked in, any ideas?
xxx
|
by: mibbsin |
last post by:
Hi all !!!!
I am a new bee for programming, my question may be simple but i dont know how to do it...My senario is I have four column named DISCOUNT CODE, DATE ACTIVATED, DATE DEAVTIVATE, IS...
|
by: Rob Wilkerson |
last post by:
Surprisingly (at least to me), there doesn't seem to be a built-in
function to validate a date value (like, say, is_date()). Given that,
is there a best practice for determining whether a value is...
|
by: =?Utf-8?B?cmF1bGF2aQ==?= |
last post by:
Hi:
(I know there are lots of different ways to do it) no regex, please
if we have string date yyMMdd "080230" whats the best way to test for valid
date ?
Feb will never have 30 days,
2)
if...
|
by: vs2k8 |
last post by:
Hello guys,
New to this forum and new to access programing, my issue is
I am comparing 2 date fields, I have to validate that Order Rcvd Dt should be less then Ord Comp date and Order Comp date...
|
by: ejrtree15 |
last post by:
i am new to java and programming all together. any help would be appreciated.
i need to get it to input the date as so: mm/dd/yyyy and then output the date as so: February 29, 2004 is a date in a...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |