Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to make Date Range variable in VBA SQL Query
I have the following code that pulls information from my time and billing
database and drops it into a pivot table to summarize everything by Partner (CppLname). What I want to do is to make the date range variable but, due to the format of the date field in the SQL database (date plus time combined) I'm running into problems. Here's the code: With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal) .Connection = Array(Array( _ "ODBC;Description=Margvpm SQL;DRIVER=SQL Server;SERVER=MARGFPS01;UID=;PWD=;APP=Microsoft® Access;WSID=;D" _ ), Array("ATABASE=margvpm")) .CommandType = xlCmdSql .CommandText = Array( _ "SELECT Clients.CPPLname, WIP.Wdate, WIP.Whours, WIP.Wfee, WIP.Wrate" & Chr(13) & "" & Chr(10) & "FROM margvpm.dbo.Clients Clients, margvpm.dbo.WIP WIP" & Chr(13) & "" & Chr(10) & "WHERE WIP.WCltID = Clients.ID AND ((WIP.Wdate{ts '2005-09-30 00:00:00'} And" _ , _ " WIP.Wdate<={ts '2006-09-30 00:00:00'}))" & Chr(13) & "" & Chr(10) & "ORDER BY Clients.CPPLname, WIP.Wdate" _ ) .CreatePivotTable TableDestination:="[Book2]Sheet1!R3C1", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 End With The WHERE statement where it loks at the WIP.wdate field is the problem. How can I drop a variable into this? I've tried a couple of solutions I found here but nothing has worked (the .CreatePivotTable section crashes, presumably due to incorrect or no data returned). HELP!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to make Date Range variable in VBA SQL Query
s1 = "2005-09-30 00:00:00"
s2 = "2006-09-30 00:00:00" . . . WIP.WCltID = Clients.ID AND ((WIP.Wdate{ts '" & s1 & "'} And" _ , _ " WIP.Wdate<={ts '" & s2 & "'}))" & Chr(13) & "" & Chr(10) .. . . -- Regards, Tom Ogilvy "Tim French" wrote: I have the following code that pulls information from my time and billing database and drops it into a pivot table to summarize everything by Partner (CppLname). What I want to do is to make the date range variable but, due to the format of the date field in the SQL database (date plus time combined) I'm running into problems. Here's the code: With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal) .Connection = Array(Array( _ "ODBC;Description=Margvpm SQL;DRIVER=SQL Server;SERVER=MARGFPS01;UID=;PWD=;APP=Microsoft® Access;WSID=;D" _ ), Array("ATABASE=margvpm")) .CommandType = xlCmdSql .CommandText = Array( _ "SELECT Clients.CPPLname, WIP.Wdate, WIP.Whours, WIP.Wfee, WIP.Wrate" & Chr(13) & "" & Chr(10) & "FROM margvpm.dbo.Clients Clients, margvpm.dbo.WIP WIP" & Chr(13) & "" & Chr(10) & "WHERE WIP.WCltID = Clients.ID AND ((WIP.Wdate{ts '2005-09-30 00:00:00'} And" _ , _ " WIP.Wdate<={ts '2006-09-30 00:00:00'}))" & Chr(13) & "" & Chr(10) & "ORDER BY Clients.CPPLname, WIP.Wdate" _ ) .CreatePivotTable TableDestination:="[Book2]Sheet1!R3C1", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 End With The WHERE statement where it loks at the WIP.wdate field is the problem. How can I drop a variable into this? I've tried a couple of solutions I found here but nothing has worked (the .CreatePivotTable section crashes, presumably due to incorrect or no data returned). HELP!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remote query & date range? | Excel Programming | |||
How do I enter a date range ie -7 from current date in MS QUERY | Excel Discussion (Misc queries) | |||
Query on Date range does not qualify the Year | Excel Programming | |||
'ActiveWorkbook.Names.Add Name:' how to make range variable? | Excel Programming | |||
Changing fixed date with a variable in a ODBC Query | Excel Programming |