Im trying to organise an SQL Query from a Visual Basic platform. I want to
search a booking table and select Kennels that do not have conflicting
bookings. The user is entering the dates (DateFrom & DateTo) in text boxes
which are read to variables defined as string type. I have used the
following code but get some erratic answers.
Can you help me with this code?
Can I use the OVERLAPS condition in SQL in VB6?
Dim DateSQL As String, KennelCost As Currency, DateFrom As String, DateTo As
Dim LengthOfStay As Integer
DateFrom = txtDateFrom
DateTo = txtDateTo
LengthOfStay = DateDiff("d", DateFrom, DateTo)
If LengthOfStay = 0 Then
LengthOfStay = LengthOfStay + 1
txtLengthOfStay.Text = LengthOfStay
DateSQL = "SELECT Kennels.[Kennel ID], Kennels.[Kennel Name] " & _
" FROM Kennels INNER JOIN Bookings " & _
" ON Kennels.[Kennel ID] = Bookings.[Kennel ID] " & _
" WHERE Bookings.[Date From] < '" & DateFrom & "' " & _
" AND Bookings.[Date To] < '" & DateFrom & "' " & _
" OR Bookings.[Date From] > '" & DateTo & "' " & _
" AND Bookings.[Date To] > '" & DateTo & "'"
datDate.RecordSource = DateSQL
Thanks in advance