Excell cell in sql where clause

**cacoyle45** · 05-26-2011, 08:08 AM

thanks for your reply, i did everything as suggested but it keeps coming up with a runtime error 1004?

any idea what the problem is?

**Macdave_19** · 05-26-2011, 08:10 AM

firstly what format is the date field in the sql table?

**cacoyle45** · 05-26-2011, 08:21 AM

i've changed the month field to date field to test the formatting command

this is what i have now

Sub Macro1()

Dim MyDate As Date

MyDate = Worksheets("Sheet1").Range("D2").Value
MsgBox Format(MyDate, "YYYY-MM-DD")

With ActiveWorkbook.Connections("sales").ODBCConnection
  .BackgroundQuery = True
  .CommandText = Array( _
  "SELECT * FROM `table1`.`pivot-1month` WHERE `Date` = " & MyDate)
  .CommandType = xlCmdSql
  .Connection = "ODBC;DSN=Database;"
  .RefreshOnFileOpen = False
  .SavePassword = False
  .SourceConnectionFile = ""
  .ServerCredentialsMethod = xlCredentialsMethodIntegrated
 .AlwaysUseConnectionFile = False
End With

With ActiveWorkbook.Connections("sales")
 .Name = "salesdata"
 .Description = "Last Week"
End With

End Sub

i added a message box to test the date output and that is ok, and i also specified the sheet for the date input - but the problem is at the commandtext section for some reason?

**Macdave_19** · 05-26-2011, 08:34 AM

Try This

Sub Macro1()

Dim MyDate As Date

MyDate = Worksheets("Sheet1").Range("D2").Value
MsgBox Format(MyDate, "YYYY-MM-DD")

With ActiveWorkbook.Connections("sales").ODBCConnection
  .BackgroundQuery = True
  .CommandText = Array( _
  "SELECT * FROM `table1`.`pivot-1month` WHERE `Date` = " & Format(MyDate, "YYYY-MM-DD"))
  .CommandType = xlCmdSql
  .Connection = "ODBC;DSN=Database;"
  .RefreshOnFileOpen = False
  .SavePassword = False
  .SourceConnectionFile = ""
  .ServerCredentialsMethod = xlCredentialsMethodIntegrated
 .AlwaysUseConnectionFile = False
End With

With ActiveWorkbook.Connections("sales")
 .Name = "salesdata"
 .Description = "Last Week"
End With

End Sub

and if that doesn't wor remove the "Array" crap and its brackets then try that

**cacoyle45** · 05-26-2011, 10:59 AM

still no luck runtime 1004 highlighting that commandtext line, do i possibly need more info on the odbc connection somewhere - not sure what the issue is at all

**Macdave_19** · 05-26-2011, 11:03 AM

Its hard to help when idont have the connection lol

once last try:

Sub Macro1()

Dim MyDate As Date

MyDate = Worksheets("Sheet1").Range("D2").Value
MsgBox Format(MyDate, "YYYY-MM-DD")

With ActiveWorkbook.Connections("sales").ODBCConnection
  .BackgroundQuery = True
  .CommandText = Array( _
  "SELECT * FROM `table1`.`pivot-1month` WHERE `Date` = " & "'" & Format(MyDate, "YYYY-MM-DD") & "'")
  .CommandType = xlCmdSql
  .Connection = "ODBC;DSN=Database;"
  .RefreshOnFileOpen = False
  .SavePassword = False
  .SourceConnectionFile = ""
  .ServerCredentialsMethod = xlCredentialsMethodIntegrated
 .AlwaysUseConnectionFile = False
End With

With ActiveWorkbook.Connections("sales")
 .Name = "salesdata"
 .Description = "Last Week"
End With

End Sub

**cacoyle45** · 05-26-2011, 11:08 AM

i added this line to output to a message box to see what the query looks like and it looks fine

Sub Macro1()

Dim MyDate As Date

MyDate = Worksheets("Sheet1").Range("D2").Value
MyDate2 = Format(MyDate, "YYYY-MM-DD")
brackets = "'"
Test1 = "SELECT * FROM pivot1month WHERE Date = '" & MyDate2 & "'"
MsgBox Test1


With ActiveWorkbook.Connections("sales").ODBCConnection
  .BackgroundQuery = True
  .CommandText = "SELECT * FROM pivot1month WHERE Date = '" & Format(MyDate, "YYYY-MM-DD") & "'"
  .CommandType = xlCmdSql
  .Connection = "ODBC;DSN=Database;"
  .RefreshOnFileOpen = False
  .SavePassword = False
  .SourceConnectionFile = ""
  .ServerCredentialsMethod = xlCredentialsMethodIntegrated
 .AlwaysUseConnectionFile = False
End With

With ActiveWorkbook.Connections("sales")
 .Name = "sales"
 .Description = "Last Week"
End With

End Sub

really stumped! i appreciate you taking the time to help thought this would be straight forward :s

**Macdave_19** · 05-26-2011, 11:12 AM

what about:

Sub Macro1()

Dim MyDate As Date

MyDate = Worksheets("Sheet1").Range("D2").Value
MyDate2 = Format(MyDate, "YYYY-MM-DD")
brackets = "'"
Test1 = "SELECT * FROM pivot1month WHERE Date = '" & MyDate2 & "'"
MsgBox Test1


With ActiveWorkbook.Connections("sales").ODBCConnection
  .BackgroundQuery = True
  .CommandText = Test1
  .CommandType = xlCmdSql
  .Connection = "ODBC;DSN=Database;"
  .RefreshOnFileOpen = False
  .SavePassword = False
  .SourceConnectionFile = ""
  .ServerCredentialsMethod = xlCredentialsMethodIntegrated
 .AlwaysUseConnectionFile = False
End With

With ActiveWorkbook.Connections("sales")
 .Name = "sales"
 .Description = "Last Week"
End With

End Sub