search for time

Results 1 to 2 of 2

Thread: search for time

  1. #1
    Join Date
    Dec 1969

    Default search for time

    i m writing a program about facility booking but i have problem in my sql statement for searching the facility, booking date, start time and end time. <BR><BR>my sql statement: <BR><BR>sql = "SELECT * FROM tblbooking WHERE facility=&#039;"&facility&"&#039; and bookDate=&#039;" & bookDate & "&#039; and startTime=#"&startTime&"# and endTime=#"&endTime&"#" <BR><BR>if Not rs.eof then <BR><BR>response.write "Time is not available" <BR><BR>else <BR><BR>response.write "Time is available" <BR><BR>end if <BR><BR>My problem is this sql statement cannot handle the people who book the time between start time and end time. If the 1st people book the time from 10am to 12pm, so the 2nd people who want book from 11am to 12pm sure cannot make it, but my program still come out the response like "Time is available". So hope anyone can help me on this or suggest other way to search the time. <BR><BR>thank. <BR>

  2. #2
    Join Date
    Dec 1969

    Default Just work out the logic...

    Let&#039;s think about it:<BR><BR>*WHEN* do you get an overlap of schedules???<BR><BR>Well, if the first event is all over before the second one even starts, that&#039;s okay, isn&#039;t it? (First event: 10AM to 12PM. Second event: 12PM to 2PM. Okay!)<BR><BR>So, posed as a question: "Does the 1st event finish before the 2nd one starts?"<BR><BR>But wait! Suppose the 2nd event went from 9AM to 10AM. No conflict! Right? Because the 2nd event ended before the first one started.<BR><BR>AHH! So that is our *OTHER* condition: "Does the 2nd event end before the first one starts?"<BR><BR>If you can answer YES to *either* of those questions, then the schedule for the 2nd event doesn&#039;t conflict, right?<BR><BR>SO...<BR><BR>sql = "SELECT * FROM tblbooking WHERE facility=&#039;" & facility & "&#039;" _<BR>&nbsp; &nbsp; * * & " AND bookDate=&#039;" & bookDate & "&#039;" _<BR>&nbsp; &nbsp; * * & " AND " _<BR>&nbsp; &nbsp; * * & &nbsp; &nbsp; * * "( endTime &#060;= #" & startTime2 & "#" _<BR>&nbsp; &nbsp; * * & &nbsp; &nbsp; * * " OR startTime &#062;= #" & endTime2 & "# )" <BR><BR>Does that make sense to you?<BR><BR>By the way...why are you specifying the bookDate as a *STRING*??? It, *too*, should be an Access Date/Time field, and then you&#039;d do<BR><BR>&nbsp; &nbsp; ...<BR>&nbsp; &nbsp; & " AND bookDate = #" & bookDate & "#" _<BR>&nbsp; &nbsp; ...<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts