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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing fixed date with a variable in a ODBC Query
Working with queries the strings are getting very long and difficult to read. To keep rour CODE readable, try splitting the lines and the components of the commandstring. For read & EDITability, I've tried to split up the COMMAND String. and used SQL's alias feature to shorten the table name in the from clause. Linefeeds are NOT needed to the SQL parser and I've left them out. (but DO note I've included spaces around the keywords. I contruct the sComm string in the beginning in order to isolate string manipulation problems from the query building and make the code easier to debug. use DEBUG.print sComm to check your total string in the IMMEDIATE pane. Sub Step1_Editability() Dim sConn$, sCommand$ Dim sSELECT, sFROM, sWHERE, sGROUP, sORDER sConn = "ODBC;DRIVER=SQL Server;SERVER=AAA_BBBBBBB;" & _ "UID=TTTTTTT;PWD=TTTTTTT;APP=Microsoft Office XP;" & _ "WSID=TTTTTTT;DATABASE=reports" sSELECT = " SELECT " & _ "CSI.Contract,CSI.A,CSI.Currency,CSI.Payment_Due_D ate," & _ "CSI.Debt_ID,CSI.Principal,CSI.Activation_Date ," & _ "CSI.Units,CSI.Vehicle_Group " sFROM = " FROM " & _ "reports.dbo.Controlling_stop_invoice CSI" sWHERE = " WHERE " & _ "CSI.Debt_ID='21') " & _ "AND (CSI.Activation_Date<={ts '2003-09-17 00:00:00'})" sGROUP = "" sORDER = " ORDER BY " & _ "CSI.Contract" sComm = sSELECT & sFROM & sWHERE & sGROUP & sORDER With ActiveSheet.QueryTables.Add( _ Connection:=sConn, Destination:=Range("A1")) ..CommandText = sComm .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 Application.DisplayAlerts = True End Sub Sub Step2_Flexibility() Dim sConn$, sCommand$ Dim sS$, sF$, sW$, sG$, sO$ 'same as above, WITH following Dim sID$, sDate$ sID = 21 sDate = Format(Now, "yyyy\-mm\-dd hh\:mm") sW = " WHERE " & _ "CSI.Debt_ID='" & sID & _ "') AND (CSI.Activation_Date<={ts '" & _ sDate & "'})" End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Hande & Tolga" wrote: 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. |
Reply |
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) |