reading/inserting variable dates within a macro
 

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"

reading/inserting variable dates within a macro
 

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)

"Dan Wasser" wrote in message
...
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'})

reading/inserting variable dates within a macro
 

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

-----Original Message-----
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)

"Dan Wasser" wrote
in message
...
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'})


.

reading/inserting variable dates within a macro
 

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" wrote in message
...
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

-----Original Message-----
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)

"Dan Wasser" wrote
in message
...
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'})


.

reading/inserting variable dates within a macro
 

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

-----Original Message-----
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" wrote
in message
...
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

-----Original Message-----
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)

"Dan Wasser"
wrote
in message
...
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'})


.



.

reading/inserting variable dates within a macro
 

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.

-----Original Message-----
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

-----Original Message-----
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" wrote
in message
...
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

-----Original Message-----
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)

"Dan Wasser"
wrote
in message
...
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'})


.



.

.

reading/inserting variable dates within a macro
 

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" wrote in message ...
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.

-----Original Message-----
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

-----Original Message-----
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" wrote
in message
...
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

-----Original Message-----
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)

"Dan Wasser"
wrote
in message
...
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'})


.



.

.

reading/inserting variable dates within a macro
 

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

-----Original Message-----
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" wrote in message
...
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.

-----Original Message-----
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

-----Original Message-----
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"
wrote
in message
...
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

-----Original Message-----
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)

"Dan Wasser"
wrote
in message
...
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'})


.



.

.

.