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!!!!

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!!!!