How to make this into VARIABLE

[JKwan]

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.

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

[Jacob Hilderbrand]

FYI

Dim FromDate, ToDate As Date

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

Try something like this.

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

[JKwan]

That worked great DRJ, THANKS

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