How to make this into VARIABLE

I am having problems making this into a "variable" query, hope someone can help.

As the way the query sits, it will only retrieve data from Jan 01 2000 to Feb 15 2000. I started on getting monkeying around with the parameters, just kept on getting SQL error. Cell A2 is from date and B2 is the end date.

:confused:

Code:

---

sub test
      Dim FromDate, ToDate As Date
Range("A3:B1000").Select
      Selection.Delete
FromDate = Format(Cells(2, 1).Value, "yyyy-mm-dd") & " 00:00:00"
      ToDate = Format(Cells(2, 2).Value, "yyyy-mm-dd") & " 00:00:00"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
          "ODBC;DSN=FetchDEV;Description=Fetch DEV;UID=SYSTEM;APP=Microsoft Office XP;WSID=DEV;DATABASE=DevDB;Trusted_C" _
          ), Array("onnection=Yes")), Destination:=Range("A3"))
          .CommandText = Array( "SELECT NonHourlyPrice.PriceDate, NonHourlyPrice.ClosePrice" & Chr(13) & "" & Chr(10) & "FROM _
FetchDEV.dbo.NonHourlyPrice NonHourlyPrice" & Chr(13) & "" & Chr(10) & "WHERE (NonHourlyPrice.PriceKeyID=65) AND (NonHourlyPrice.Price" , _
 "Date>={ts '2000-01-01 00:00:00'} And NonHourlyPrice.PriceDate<={ts '2000-02-15 00:00:00'})" & Chr(13) & "" & Chr(10) & _
 "ORDER BY NonHourlyPrice.PriceDate")
          .Name = "Fetch"
          .FieldNames = True
          .RowNumbers = False
          .FillAdjacentFormulas = False
          .PreserveFormatting = True
          .RefreshOnFileOpen = False
          .BackgroundQuery = True
          .RefreshStyle = xlOverwriteCells
          .SavePassword = True
          .SaveData = True
          .AdjustColumnWidth = True
          .RefreshPeriod = 0
          .PreserveColumnInfo = True
          .Refresh BackgroundQuery:=False
      End With
 end sub

---

FYI

Code:

---

Dim FromDate, ToDate As Date

---

Only specifies the data type of Date to ToDate. FromDate is Variant.

Try something like this.

Code:

---

Dim FromDate As String
Dim ToDate As String
"Date>={ts '" & FromDate & "'} And NonHourlyPrice.PriceDate<={ts '" & ToDate & "'})" & Chr(13) & "" & Chr(10) & "ORDER BY NonHourlyPrice.PriceDate" _

---

That worked great DRJ, THANKS:hi:

I guess the only question that I have is that why dim as STRING? I thought that my data types are DATES.