From Excel, I need a data query to use the value of a
cell. It should read something like this:

SELECT Count(*) FROM Orders WHERE SALE_DATE='Sheet1'!B5

I cannot get the query to read anthing from the Excel
sheet.

Isn't there an obvious way to do this????

Thanks!!!!

Ted

WHERE SALE_DATE=?

The question mark is a place holder for the parameter. If you put the
question mark in there, it will default to a prompt-type parameter. You can
change that to cell ref-type parameter in Excel. See here

http://www.dicks-clicks.com/excel/Ex...htm#Parameters

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"TedGrier" wrote in message
...
From Excel, I need a data query to use the value of a
cell. It should read something like this:

SELECT Count(*) FROM Orders WHERE SALE_DATE='Sheet1'!B5

I cannot get the query to read anthing from the Excel
sheet.

Isn't there an obvious way to do this????

Thanks!!!!

Thanks... This almost works.
I am getting an error this is the wrong data type for the
parameter. How can I get it to accept a date like
6/20/2004?

Thanks So Much!!!!

-----Original Message-----
Ted

WHERE SALE_DATE=?

The question mark is a place holder for the parameter.
If you put the
question mark in there, it will default to a prompt-type
parameter. You can
change that to cell ref-type parameter in Excel. See
here

http://www.dicks-
clicks.com/excel/ExternalData6.htm#Parameters

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"TedGrier" wrote
in message
...
From Excel, I need a data query to use the value of a
cell. It should read something like this:

SELECT Count(*) FROM Orders WHERE SALE_DATE='Sheet1'!B5

I cannot get the query to read anthing from the Excel
sheet.

Isn't there an obvious way to do this????

Thanks!!!!


.

Dick. Your answer on the Excel newsgroup was excellent,
and I am truly grateful. But I am in a crisis and need
help with just one small step. When I assigned the
parameter to lookup data from a cell, I get an error
message:

"Bad parameter type. Microsoft Excel is expecting a
different king of value than what was provided."


The cell is formatted as date MM/DD/YYYY.

I have tried desperately to using apostrophies to no
avail.

Is there a secret to reading dates into parameter fields?



Thank You Kindly,

Ted Grier



-----Original Message-----
Ted

WHERE SALE_DATE=?

The question mark is a place holder for the parameter.
If you put the
question mark in there, it will default to a prompt-type
parameter. You can
change that to cell ref-type parameter in Excel. See
here

http://www.dicks-
clicks.com/excel/ExternalData6.htm#Parameters

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"TedGrier" wrote
in message
...
From Excel, I need a data query to use the value of a
cell. It should read something like this:

SELECT Count(*) FROM Orders WHERE SALE_DATE='Sheet1'!B5

I cannot get the query to read anthing from the Excel
sheet.

Isn't there an obvious way to do this????

Thanks!!!!


.

Ted


"Bad parameter type. Microsoft Excel is expecting a
different king of value than what was provided."


The cell is formatted as date MM/DD/YYYY.

I have tried desperately to using apostrophies to no
avail.

Is there a secret to reading dates into parameter fields?



There's no secret that I know. I believe it all depends on what kind of
field the source is expecting. I've had no trouble using dates as
parameters for Access and Timberline which is what I query. No doubt there
are some databases that are going to be particular about what you provide as
a parameter.

The format of the cell should not matter, it will be looking at the
underlying value. If you look at the query results (with no parameters) in
MS Query, what does the date field look like? If it looks like 2004-07-19,
then you might have to pass it a string in the same format, rather than a
what Excel considers a date. You said you had used apostrophes which may be
the right answer. You'll just have to experiment with different strings to
find which one works.

What kind of database are you querying?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

It is SQL Server 2000.
The column SALE_DATE is smalldatetime.


-----Original Message-----
Ted


"Bad parameter type. Microsoft Excel is expecting a
different king of value than what was provided."


The cell is formatted as date MM/DD/YYYY.

I have tried desperately to using apostrophies to no
avail.

Is there a secret to reading dates into parameter
fields?



There's no secret that I know. I believe it all depends
on what kind of
field the source is expecting. I've had no trouble
using dates as
parameters for Access and Timberline which is what I
query. No doubt there
are some databases that are going to be particular about
what you provide as
a parameter.

The format of the cell should not matter, it will be
looking at the
underlying value. If you look at the query results
(with no parameters) in
MS Query, what does the date field look like? If it
looks like 2004-07-19,
then you might have to pass it a string in the same
format, rather than a
what Excel considers a date. You said you had used
apostrophes which may be
the right answer. You'll just have to experiment with
different strings to
find which one works.

What kind of database are you querying?

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.

"TedGrier" wrote ...

It is SQL Server 2000.
The column SALE_DATE is smalldatetime.

Execute this on the server:

CREATE PROCEDURE
MyStoredProc
(
@effective_date
)
AS
SELECT Count(*)
FROM Orders
WHERE SALE_DATE=@effective_date
;

Execute this in MS Query:

EXEC MyStoredProc '20 JUN 2004'

Jamie.

--