PDA

View Full Version : [SOLVED] How to make this into VARIABLE



JKwan
12-01-2004, 04:46 PM
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:



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
12-01-2004, 06:58 PM
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
12-02-2004, 09:21 AM
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.