Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query - Parameter Bug
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!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query - Parameter Bug
SELECT Count(*) FROM Orders WHERE SALE_DATE="'" & format( _
Range("Sheet1!A1").Value2,"dd mmm yyyy") & "'" -- Regards, Tom Ogilvy "TedGrier" wrote in message ... 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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Parameter Query | Excel Discussion (Misc queries) | |||
Parameter query in MS Query from Excel | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |