Excell cell in sql where clause

[cacoyle45]

hi guys

complete newbie to this - looking to create a pivot table from an mysql db hosted remotely, i have everything working at the moment but i would like to expand on it so that a user can enter a date on the excel sheet and click refresh and the query will use the date range from that cell

eg select * from table1 where date = C1

this is very sloppy but i've got this far

Sub SetStartDate()
Dim rStartDate As Range
    Set rStartDate = Range("D1")
End Sub
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+M
'
    
    With ActiveWorkbook.Connections("sales_weekly").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "SELECT * FROM `db`.`table1` WHERE `Month` = ") rStartDate
        .CommandType = xlCmdSql
        .Connection = "ODBC;DSN=Database;"
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("table1")
        .Name = "table1"
        .Description = "Last Week"
    End With
End Sub

any help with this would be greatly appreciated!!! thanks

[Macdave_19]

Firstly You need to wrap your code!

Secondly looks like your almost there buddy:

Sub Macro1()

Dim MyDate as Date

MyDate = Range("C1").value

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

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

End Sub

However i Notice that the field you are apply the where clause to is called "Month" that what format is that in? e.g "February" or "Feb" or "02" or is it a full date? you'll need to format the value from cell C1 to match the field format and if it contaings letters you'll also have to wrap it in apostraphes (however its spelt?)

E.g.

 "SELECT * FROM `db`.`table1` WHERE `Month` = " & Format(MyDate,"DD-MMM-YY"))

Hope this helps?

[cacoyle45]

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]

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

[cacoyle45]

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]

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]

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