MS Query Using Vairable From A Cell

T

TedGrier

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!!!!
 
T

TedGrier

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!!!!
 
T

TedGrier

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
 
D

Dick Kusleika

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?
Click to expand...

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?
 
J

Jamie Collins

...
It is SQL Server 2000.
The column SALE_DATE is smalldatetime.
Click to expand...

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.

--
 
Top