Trying to make Date Range variable in VBA SQL Query

[Tim French]

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:=xlExternal)
.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!!!!

 

[Tom Ogilvy]

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)

.. . .