SQL Syntax Error in Query
 

I have a query which I wnat to run between two dates (yyyy-mm-dd hh.mm.ss
format) which are in two specified cells on a workbook, tried the routine
below but when macro gets to " .Refresh BackgroundQuery:=False" line at
bottom I get Run Time error 1004 - SQL Syntax Error (Full code below).

Please help.

Sub GetData()
'
' GetData Macro
' Macro recorded 30/11/2007 by
'

Worksheets("Push Data").Activate

Dim StartDate As Date, EndDate As Date
StartDate = Range("A1").Value
EndDate = Range("A2").Value

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DL BATT;UID=sa;PWD=panelview900;APP=Microsoft®
Query;WSID=DGD7K22J;DATABASE=SerialLinks" _
, Destination:=Range("A3"))
.CommandText = Array( _
"SELECT vwOvenData.PushDateTime, vwOvenData.OvenID,
vwOvenData.MaxPush1, vwOvenData.MaxPush2" & Chr(13) & "" & Chr(10) & "FROM
SerialLinks.dbo.vwOvenData vwOvenData" & Chr(13) & "" & Chr(10) & "WHERE
(vwOvenData.PushDateTime={'" & Format(StartDate, "yyyy-mm-dd hh:mm:ss") &
"'} And " _
, _
"vwOvenData.PushDateTime<{'" & Format(EndDate, "yyyy-mm-dd
hh:mm:ss") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY
vwOvenData.PushDateTime" _
)
.Name = "Query from DL BATT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

SQL Syntax Error in Query
 

I think you need the ts and the space

from
PushDateTime={'" & Format(StartDate, "yyyy-mm-dd hh:mm:ss")
to
PushDateTime={ts '" & Format(StartDate, "yyyy-mm-dd hh:mm:ss")

from
PushDateTime<{'" & Format(EndDate, "yyyy-mm-dd hh:mm:ss"
to
PushDateTime<{ts '" & Format(EndDate, "yyyy-mm-dd hh:mm:ss"

"jim hardwick" wrote:

I have a query which I wnat to run between two dates (yyyy-mm-dd hh.mm.ss
format) which are in two specified cells on a workbook, tried the routine
below but when macro gets to " .Refresh BackgroundQuery:=False" line at
bottom I get Run Time error 1004 - SQL Syntax Error (Full code below).

Please help.

Sub GetData()
'
' GetData Macro
' Macro recorded 30/11/2007 by
'

Worksheets("Push Data").Activate

Dim StartDate As Date, EndDate As Date
StartDate = Range("A1").Value
EndDate = Range("A2").Value

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DL BATT;UID=sa;PWD=panelview900;APP=Microsoft®
Query;WSID=DGD7K22J;DATABASE=SerialLinks" _
, Destination:=Range("A3"))
.CommandText = Array( _
"SELECT vwOvenData.PushDateTime, vwOvenData.OvenID,
vwOvenData.MaxPush1, vwOvenData.MaxPush2" & Chr(13) & "" & Chr(10) & "FROM
SerialLinks.dbo.vwOvenData vwOvenData" & Chr(13) & "" & Chr(10) & "WHERE
(vwOvenData.PushDateTime={'" & Format(StartDate, "yyyy-mm-dd hh:mm:ss") &
"'} And " _
, _
"vwOvenData.PushDateTime<{'" & Format(EndDate, "yyyy-mm-dd
hh:mm:ss") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY
vwOvenData.PushDateTime" _
)
.Name = "Query from DL BATT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

SQL Syntax Error in Query
 

Thanks a lot thats sorted it!

Jim

"Joel" wrote:

I think you need the ts and the space

from
PushDateTime={'" & Format(StartDate, "yyyy-mm-dd hh:mm:ss")
to
PushDateTime={ts '" & Format(StartDate, "yyyy-mm-dd hh:mm:ss")

from
PushDateTime<{'" & Format(EndDate, "yyyy-mm-dd hh:mm:ss"
to
PushDateTime<{ts '" & Format(EndDate, "yyyy-mm-dd hh:mm:ss"

"jim hardwick" wrote:

I have a query which I wnat to run between two dates (yyyy-mm-dd hh.mm.ss
format) which are in two specified cells on a workbook, tried the routine
below but when macro gets to " .Refresh BackgroundQuery:=False" line at
bottom I get Run Time error 1004 - SQL Syntax Error (Full code below).

Please help.

Sub GetData()
'
' GetData Macro
' Macro recorded 30/11/2007 by
'

Worksheets("Push Data").Activate

Dim StartDate As Date, EndDate As Date
StartDate = Range("A1").Value
EndDate = Range("A2").Value

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DL BATT;UID=sa;PWD=panelview900;APP=Microsoft®
Query;WSID=DGD7K22J;DATABASE=SerialLinks" _
, Destination:=Range("A3"))
.CommandText = Array( _
"SELECT vwOvenData.PushDateTime, vwOvenData.OvenID,
vwOvenData.MaxPush1, vwOvenData.MaxPush2" & Chr(13) & "" & Chr(10) & "FROM
SerialLinks.dbo.vwOvenData vwOvenData" & Chr(13) & "" & Chr(10) & "WHERE
(vwOvenData.PushDateTime={'" & Format(StartDate, "yyyy-mm-dd hh:mm:ss") &
"'} And " _
, _
"vwOvenData.PushDateTime<{'" & Format(EndDate, "yyyy-mm-dd
hh:mm:ss") & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY
vwOvenData.PushDateTime" _
)
.Name = "Query from DL BATT"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub