MS Query Command Text Parameter

Question

Using MS Query to retreive data in to Excel. Can i use a cell value as a parameter in the Where clause of the 'Command Text' in the MS Query Connection Properties? What would be the correct syntax to use if this it possible?

I have tried variations of the below

SELECT * FROM "dbo"."vendor" WHERE vend_num = ("Sheet2").("A1").Value

Answer 1

Change you query to:

SELECT * FROM "dbo"."vendor" WHERE vend_num = ?

Return the results to Excel, you will be prompted for a value just type anything for now. In Excel make sure you have the active cell is somewhere in the query results, go to the Table Tools DESIGN ribbon, click the small down pointing arrow below the refresh icon and select connection properties.

Click the Definition tab and then the Parameters button, you will then see a dialog box listing each parameter on the left-hand side and on the the right will be be three options for how you want that parameter populated.

First option is to prompt for a value, second is to use a hard-coded value and last which is what you want is the option to use a value from a cell. You also have the option to refresh the query automatically if the cell value changes.