reading/inserting variable dates within a macro

[Dan Wasser]

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_Connection=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"

 

[Bob Phillips]

Dan,

Not tested, but here's a stab

WHERE (STAY.S_ADATE>={Format(Range("A1"),"yyyy-mm-dd")} And
STAY.S_ADATE<={Format(Range("A2"),"yyyy-mm-dd")})

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

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'})

 

[Dan Wasser]

Thanks. Nice try. But it didn't work. I get this error:
Compile Error: Expected: list separator or )

It seems like it doesn't like the quotation marks around
the A1 (or A2). I'll keep playing with it but if you or
someone else has other suggestions, I'd appreciate it. Dan

 

[Bob Phillips]

Sorry I was of no help. If you type ?Format(Range("A1"),"yyyy-mm-dd") in the
immediate window, it works okay. The curly braces threw me, have you tried
removing them?

Other than that, I hope someone else can help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dan Wasser]

Thanks, Bob, for your attempts. Please read below. I
understand if you don't want to continue to deal with
this. Maybe someone else out there has a solution. Dan

-------------------
Hmmmm. Maybe I was coming at this from the wrong
direction. I tried your latest solution (?Format etc.).
The macro then allowed me to enter two parameters (dates,
I presume). I entered the "from" and "to" dates. It
still bombs out ... at the last line (below):
..Refresh BackgroundQuery:=False

It bombs out with "true" too. When I comment it out, it
doesn't bomb out but no data gets returned.

.Name = "Query from V1DATA"
.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

If anyone wants to try, please let me know. Thanks. Dan

 

[Dan Wasser]

By the way, I had to remove the quotation marks around the
A1 and the yyyy-mm-dd in order to avoid that Compile error.

 

[onedaywhen]

Here's my stab, also untested:

"WHERE (STAY.S_ADATE>='" & Format(Range("A1"),"yyyy-mm-dd") & "' And
STAY.S_ADATE<='" & Format(Range("A2"),"yyyy-mm-dd") & "')
AND (STAY.S STATUS='HIST') A"

--

 

[Dan Wasser]

Woo-hoo! It worked! Thanks so much! I owe you one!
Dan