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
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.
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") & "'"