MS Query - Parameter Bug

[Guest]

I have an Excel 2002 worksheet connected to a SQL Server 2000 database.
The data query is simply:
SELECT Count(*) FROM Orders WHERE SALE_DATE=[Parameter1]

When prompted for the parameter value, I can manually enter a date 6/20/2004 and the query executes perfectly.

But when I try to read a date vaule from a cell, I get an error message "data type mismatch". I have tried all date formats in the cell to no avail. The column SALE_DATE is a smalldatetime type.

It appears to be a bug in the Parameter global variable Microsoft uses to pass value from the sheet to the query. It works for integers and strings but not dates. There must be some crazy way of encapsulating the date value?

Any suggestions?

Thanks!!!!

 

[Tom Ogilvy]

SELECT Count(*) FROM Orders WHERE SALE_DATE="'" & format( _
Range("Sheet1!A1").Value2,"dd mmm yyyy") & "'"

--
Regards,
Tom Ogilvy

I have an Excel 2002 worksheet connected to a SQL Server 2000 database.
The data query is simply:
SELECT Count(*) FROM Orders WHERE SALE_DATE=[Parameter1]

When prompted for the parameter value, I can manually enter a date

6/20/2004 and the query executes perfectly.

But when I try to read a date vaule from a cell, I get an error message

"data type mismatch". I have tried all date formats in the cell to no avail.
The column SALE_DATE is a smalldatetime type.

It appears to be a bug in the Parameter global variable Microsoft uses to

pass value from the sheet to the query. It works for integers and strings
but not dates. There must be some crazy way of encapsulating the date
value?