Very Computer

Hi there

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?
or
Can I use the OVERLAPS condition in SQL in VB6?

Dim DateSQL As String, KennelCost As Currency, DateFrom As String, DateTo As
String
Dim LengthOfStay As Integer
DateFrom = txtDateFrom
DateTo = txtDateTo

LengthOfStay = DateDiff("d", DateFrom, DateTo)
    If LengthOfStay = 0 Then
        LengthOfStay = LengthOfStay + 1
    End If
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
datDate.Refresh

Thanks in advance

Tim D

Tim,

There is a well-defined hierarchy of resolving ANDs and ORs in the WHERE
clause, but frankly I always forget it. Use parentheses to avoid problems.

The OVERLAPS option is, I believe part of the ANSI SQL-92 standard, but I
don't know of any RDBMS that has yet implemented it. Certainly MS SQL Server
does not yet support it.

Quote:> Hi there

> 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?
> or
> Can I use the OVERLAPS condition in SQL in VB6?

> Dim DateSQL As String, KennelCost As Currency, DateFrom As String, DateTo
As
> String
> Dim LengthOfStay As Integer
> DateFrom = txtDateFrom
> DateTo = txtDateTo

> LengthOfStay = DateDiff("d", DateFrom, DateTo)
>     If LengthOfStay = 0 Then
>         LengthOfStay = LengthOfStay + 1
>     End If
> 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
> datDate.Refresh

> Thanks in advance

> Tim D

It's fairly easy: AND is a multiplication (1*0=0), OR is an addition
(1+0=0), a AND b OR c AND d is the same as (a AND b) OR (c AND d).

In general programming AND and OR may appear to have about the same
frequency. In SQL queries, AND is much more common. (If it's in your
queries, I am not surprised to hear that you have performance problems.)

--

This is an incomplete mess.