MS Query Using Vairable From A Cell

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

 

[Dick Kusleika]

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

 

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

 

[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

 

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

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?

 

[TedGrier]

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

 

[Jamie Collins]

...

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.

--