sqlexcelvba

How to search for dates between two Excel worksheet cells?

VBA - Searching in SQL between two dates in two cells in worksheet.

Sub giveMeData()
    Columns("A:A").SelectSelection.Replace What:="  ", Replacement:="", LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ReplaceFormat:=False

    Dim sql As String

    sql = "select" & Chr(10) & _
      ", a.order Order_no" & Chr(10) & _", a.client Client" & Chr(10) & _", 
      a.date Delivery_Date" & Chr(10) & _" from table a" & Chr(10) & _" 
      where a.order in " & Worksheets("UM").Range("G7").Value & Chr(10)

    With ActiveWorkbook.Connections("query").ODBCConnection
        .BackgroundQuery = False
        .CommandText = sql
        .CommandType = xlCmdSql
        .Connection = "ODBC;SERVER=myserver;DRIVER={Microsoft ODBC for Oracle};uid=" & ThisWorkbook.Sheets("uwagi").Range("L1") & ";pwd=" & ThisWorkbook.Sheets("uwagi").Range("L2")
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    ActiveWorkbook.Connections("query").Refresh

End Sub

I want to change the last line in SQL from

" where a.order in " & Worksheets("UM").Range("G7").Value & Chr(10)

to

where a.date between " & Worksheets("UM").Range("G7").Value & Chr(10) and  & Worksheets("UM").Range("G8").Value & Chr(10)

I was trying:

"where a.date between " & Worksheets("UM").Range("G7").Value & Chr(10) & Worksheets("UM").Range("G8").Value & Chr(10)

But error happens
error

I have found:

Set MasterSht = Worksheets("Master")

Dim TempRng As String
TempRng = Replace(MasterSht.Range(Cells(1, 1), Cells(11, 5)).Address, "$", vbNullString)
    
Dim MyQuery_1 As String
Dim ReturnArray

I do not know how to manage it for my code.

Solution

  • In my experience working with dates in a SQL environment they need to be wrapped in Single quotes. Also your between statement didn't have the AND wrapped in double Quotes

    sql = "select" & Chr(10) & _
     " a.order Order_no" & Chr(10) & _
     ", a.client Client" & Chr(10) & _
     ", a.date Delivery_Date" & Chr(10) & _
     " from table a" & Chr(10) & _
     " where a.date between '" & Format(Worksheets("UM").Range("G7").Value, "yyyy-mm-dd") & "' AND '" & Format(Worksheets("UM").Range("G8").Value, "yyyy-mm-dd") & "'"