Argthtjtr

Thanks for your help, @FreeMan.

However, I am still lost with this.

Let me just start from scratch.

This is my query in summary ( I hope it is clear)

SELECT DISTINCT Q1,Q2,Q3



from(SELECT A,B,C) Q1



full outer join



(SELECT A,B,C



from tblA as a



join tblB as b



on a.aID = b.bID



join tblC as c



on b.bID = c.cID



where (a,b, cdate <= ?



group by c) Q2



ON Q1.A = Q2.B



WHERE Convert(DATETIME, Q1.[B], 103) <= ?



order by Q1.[A]



When I try to refresh my table, I get the following error message



"[Microsoft][ODBC SQL Server Driver]Invalid Parameter number" and then



"[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index"

I need a method that will allow me to run this query using the parameters in a specified cell i.e. cell C3.

I hope this makes things a bit clearer I am not familiar with VBA so all the commands advised above are confusing to me.

Original Question

*[I am a newbie when it comes to VBA and so I am struggling to get my query to run with more than one parameter.
I am using this macro as but when I run it it tells me the second parameter has not been provided.

Sub RefreshQuery()



With ActiveWorkbook.Connections("MYSERVER").OLEDBConnection

    .CommandText = "EXECUTE dbo.Tng_Market_Feed '" & Range("B2").Value & "'"

End With

ActiveWorkbook.Connections("MYSERVER").Refresh



End Sub

My questions are:-
1. How do I set the variables to date formats? I need a report to show me all transactions <= a certain date (variable parameter)
2. The location of the variable in the spreadsheet, cell b2, remains the same for both parameters. For example, I need a list of all transactions <= 31/12/2014 and a value/sum of bad debt provision as at the same date 31/12/2014.]*

Here's a quick sample of creating and setting parameters from within your code:

Sub CreateParam()



     Dim oQuery As QueryTable

     Dim oParam As Parameter



     Set oQuery = Sheet3.QueryTables(1)



     oQuery.CommandText = Replace(oQuery.CommandText, "='Berlin'", "=?")



     Set oParam = oQuery.Parameters.Add("CityParam")



     oParam.SetParam xlRange, Sheet3.Range("J1")

     oParam.RefreshOnChange = True



     oQuery.Refresh



End Sub

^{code quickly cribbed from dicks-clicks.com}

Basically you'll need to execute the .Parameter.Add() line for each parameter in your stored procedure, and you'll be good to go. Note that you don't need to write a whole Sub() to do it, you can just include the Set oParam = oQuery.Parameters.Add() line in your existing code.

You may want to dig into the the options a bit, because you can specify the type of data being sent, and can also set up an OUT parameter, as well, to accept data back from the query.