Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing fixed date with a variable in a ODBC Query
Below is the original code we use in our VB to retrieve
data directly from database. Instead of using a fixed activation date "2003-09-17 00:00:00" (please check the location in the code below) we want to replace that with a variable where it makes the code more flexible such as Temp1 where we enter or define the date to Temp1 earlier in the code. We would appreciate if someone could help us with the syntax. Actual Code ----------- With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DRIVER=SQL Server;SERVER=AAA_BBBBBBB;UID=TTTTTTT;PWD=TTTTTTT; APP=Micr osoft Office XP;WSID=TTTTTTT;DATABASE=reports" _ , Destination:=Range("A1")) .CommandText = Array( _ "SELECT Controlling_stop_invoice.Contract, Controlling_stop_invoice.A, Controlling_stop_invoice.Currency, Controlling_stop_invoice.Payment_Due_Date, Controlling_stop_invoice.Debt_ID, Controlling_stop_i" _ , _ "nvoice.Principal, Controlling_stop_invoice.Activation_Date, Controlling_stop_invoice.Units, Controlling_stop_invoice.Vehicle_Group" & Chr(13) & "" & Chr(10) & "FROM reports.dbo.Controlling_stop_invoice Controlling_stop_invoice" & Chr(13) & "" & Chr(10) & "" _ , _ "WHERE (Controlling_stop_invoice.Debt_ID='21') AND (Controlling_stop_invoice.Activation_Date<={ts '2003- 09-17 00:00:00'})" & Chr(13) & "" & Chr(10) & "ORDER BY Controlling_stop_invoice.Contract" _ ) .Name = "Query from reports_ok_20" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC Query | Excel Worksheet Functions | |||
Microsoft Query is changing a negative number into a date | Excel Discussion (Misc queries) | |||
Changing ODBC data source for a query embedded in Excel | Excel Discussion (Misc queries) | |||
Changing data source on ODBC query | Excel Discussion (Misc queries) | |||
Changing ODBC link in a query | Excel Discussion (Misc queries) |