Excell cell in sql where clause

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