SQL Query not returning correct results

[Stephen]

Hi Folks,

I've been building out a solution for my users which will allow them to
enter a date range, query a remote SQL DB via an ADO connection, and return
the results of to sheet1. I've made some great progress with the help of this
forum but I find myself perplexed as to why I do not return the correct
results when a user enters a single day as the date range for the query. For
example, If I enter 07/10/2006 for a starting date, and 07/11/2006 as an
ending date, the query returns the correct number of records for 07/10/2006.
I have verified this. But if the date range is 07/10/2006 and 07/10/2006 the
query returns an empty recordset. Below is the code I have so far...

' Define Input Date Parameters, Worksheet, and Integer object
Dim dtStartDate As String
Dim dtEndDate As String
Dim ws As Worksheet
Dim i As Integer
Dim iCount As Integer
Dim blnIsOk As Boolean

blnIsOk = False
Do Until blnIsOk
dtStartDate = InputBox("Enter a starting date for the report
range.", "Beginning Date Range", "XX/XX/XXXX")
If IsDate(dtStartDate) Then
GetDate = Format(CDate(dtStartDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop
blnIsOk = False
Do Until blnIsOk
dtEndDate = InputBox("Enter an end date for the report range.",
"Ending Date Range.", "XX/XX/XXXX")
If IsDate(dtEndDate) Then
GetDate = Format(CDate(dtEndDate), "mm/dd/yyyy")
blnIsOk = True
End If
Loop

' Create the connection object and query object
Dim dbConnection As Object
Dim strSQL As String
Set dbConnection = CreateObject("ADODB.Connection")

' Create the recordset object and initiate a new instance of it
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Sheet1.Range("A").ClearContents
Range("H6:H12").ClearContents
'Range("H7").ClearContents
'Range("H9").ClearContents

' Make the connection and run the query
dbConnection.Open "Driver={SQL Server}; Server=##;Database=##;Uid=##; Pwd=##;"
strSQL = "SELECT DISTINCT order_date,order_no,completed FROM oe_hdr WHERE
oe_hdr.order_date >= '" & dtStartDate & "' AND oe_hdr.order_date <= '" &
dtEndDate & "' ORDER BY order_no ASC"
'strSQL = "SELECT order_date,order_no,completed FROM oe_hdr WHERE order_date
BETWEEN '" & dtStartDate & "' AND '" & dtEndDate & "' ORDER BY order_no ASC"
dbConnection.Execute (strSQL)
rs.Open strSQL, dbConnection

For i = 0 To rs.Fields.Count - 1
Sheet1.Cells(1, i + 1).Value = rs.Fields(i).Name
Next
Sheet1.Range(Sheet1.Cells(1, 1), _
Sheet1.Cells(1, rs.Fields.Count)).Font.Bold = True
Sheet1.Range("A2").CopyFromRecordset rs

' Close the recordset object and release the memory space by setting the
object to nothing
rs.Close
Set rs = Nothing

' Close the connection object and release the memory space by setting the
object to nothing
dbConnection.Close
Set dbConnection = Nothing

As you can see I've tried using both >= and <=, and a BETWEEN in my query,
both with the same strange results.

Any advise is always greatly appreciated.

Thanks!