MS Query with multiple Parameters

Question

I have this SQL Query and i want the results in an Excel 2013 sheet / table

SELECT 
  [MS Part Number], [Item Name], SUM([Aantal]) AS Aantal, [Prijs]
FROM 
  (
    SELECT 
      [MS Part Number], [Item Name], SUM([Aantal]) AS Aantal, [Prijs]
    FROM 
      vwSPLAInformatie
    WHERE 
      (DATEPART(YEAR, fakdat) = '2013') 
      AND (DATEPART(QUARTER, fakdat) = '1')
      AND docnumber LIKE '%kwartaal%'
    GROUP BY 
      [MS Part Number], [Item Name], [Aantal], [Prijs]
    UNION ALL
    SELECT 
      [MS Part Number], [Item Name], SUM([Aantal]) AS Aantal, [Prijs]
    FROM 
      vwSPLAInformatie
    WHERE 
      (DATEPART(YEAR, fakdat) = '2013') 
      AND (DATEPART(MONTH, fakdat) = '1')
      AND NOT docnumber LIKE '%kwartaal%'
    GROUP BY 
      [MS Part Number], [Item Name], [Aantal], [Prijs]
  ) AS Basis
GROUP BY 
  [MS Part Number], [Item Name], [Prijs]

It works perfectly, the only thing is that i have 4 parameters that i want to connect to 3 cell values in Excel. (Year = 2013) (Quarter = 1) (Month = 2). But when i adjust my Query to the following:

 WHERE (DATEPART(YEAR, fakdat) = '?')

And i select the cell in Excel that says 2013, so basicly it should work, but it doensn't. I get the following error:

[Microsoft]ODBC SQL Server][SQL Server]Conversion failed when converting the varchar value '?' to data type in.

The value is the same, but it does not work at all. How can i fix this? I would be extremely happy when this works! In forward, many thanks.

Jeez, you have to work on your code formatting skills. The [original SQL layout](http://stackoverflow.com/revisions/15205212/1) was the very definition of unreadable. -- Also, SQL field names with spaces? :-/ — Tomalak, Mar 04 '13 at 15:43
Have you tried `WHERE DATEPART(YEAR, fakdat) = ?` (without the quotes)? Parameter placeholders are not supposed to be in enclosed by quotes. — Tomalak, Mar 04 '13 at 15:49
If i change it to ? and that is without the quotes, then i get the error [Microsoft]ODBC SQL Server][SQL Server]Invalid Parameter Number & [Microsoft]ODBC SQL Server][SQL Server]Invalid Descriptor index — BassieBas1987, Mar 06 '13 at 08:09
Hm. This [forum thread](http://www.mrexcel.com/forum/excel-questions/429136-invalid-parameter-number-error.html) and this [MSDN Social thread](http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/99e25a59-9df7-41f1-9032-3fe454326d32) suggest that it might be a bug in ODBC or Excel - parameters seem to fail for complex statements. It could be a work-around to put your SQL statement into a stored procedure and call that from Excel. — Tomalak, Mar 06 '13 at 08:36
I looked at it, it is indeed a bug with Excel using the OBDC. But it says that i need to use this: _2) Creating a query that selects from a view and then modifying the same query (in the "Definition" tab in "Connection Properties") by changing the Command text to "{CALL MyStoredProcedure(?,?)}"_ and that i can change it in my query directly. But how should that be? — BassieBas1987, Mar 06 '13 at 09:58

MS Query with multiple Parameters

0 Answers0