I'm trying to write a macro but can't figure out how to
insert a variable "from" and "to" date within the
instructions.

Here's the portion of the macro that I can't figure out:
WHERE (STAY.S_ADATE={ts '2002-12-31 00:00:00'} And
STAY.S_ADATE<={ts '2003-04-30 00:00:00'})

(The above was captured by recording a macro and using
Query Wizard. In the Query Wizard, I had to specify
a "from" and "to" date. So, I chose Dec. 31 and April
30. But the from and to dates will be hand-entered in the
spreadsheet itself and will change on-the-fly, so I don't
want to pre-define the dates within the macro.)

(The full macro is at the bottom of this message. I'm
trying to extract data from a SQL database
called "V1Data.")

But instead of having to specify a "from" and "to" date
within the macro, I want the macro to use whatever date is
defined in (from) Cell B1 and (to) Cell B2 in a worksheet
called "Date." I've tried all combinations of Range.(Date!
B1).Value and .Select, with and without quotation marks
(single and double), with and without the "ts," with and
without brackets and parenthesis and I just can't get it
to work!

Any ideas? Thanks in advance. Dan



With ActiveSheet.QueryTables.Add(Connection:=Array
(Array( _
"ODBC;DSN=V1DATA;Description=Crystal Reports
Data;UID=dwasser;APP=Microsoft®
Query;WSID=DWASSER;DATABASE=V1Data;Trusted_Connect ion=Ye" _
), Array("s;AnsiNPW=No")), Destination:=Range
("A1"))
.CommandText = Array( _
"SELECT STAY.S_ADATE, IINFO.I_ZIP, STAY.S_STATUS,
STAY.S_NIGHTS" & Chr(13) & "" & Chr(10) & "FROM
v1data.dbo.IINFO IINFO, v1data.dbo.STAY STAY" & Chr(13)
& "" & Chr(10) & "WHERE (STAY.S_ADATE={ts '2002-12-31
00:00:00'} And STAY.S_ADATE<={ts '2003-04-30 00:00:00'})
AND (STAY.S_STATUS='HIST') A" _
, "ND (STAY.S_NIGHTS=1) AND (IINFO.I_ZIP='00601'
And IINFO.I_ZIP<='00999') AND (IINFO.I_RECID =
STAY.S_IRECID)" & Chr(13) & "" & Chr(10) & "ORDER BY
IINFO.I_ZIP")
.Name = "Query from V1DATA"