Using cell values in SQL (MS Query)

[Arshavir Grigorian]

Hi,

Is there an easy way to use cell (or range) values in an MS Query SQL
- e.g. [select * from table where id = Cell("E4")]. TIA.

 

[Jim Thomlinson]

Add a prompt to your query so that it becomes parameterized.

select * from table where id = ?

Now exit the query and return to XL. If you select Data -> Import External
Data -> Parameters you will be able to assign the parameter to a cell.

 

[Arshavir Grigorian]

Thanks. A couple of issues:

1) Can't do [ ... WHERE col IN (?)] Only allows single values.

2) Can't have a parameter in a query that can't be displayed
graphically.

Are these for real?

Add a prompt to your query so that it becomes parameterized.

select * from table where id = ?

Now exit the query and return to XL. If you select Data -> Import External
Data -> Parameters you will be able to assign the parameter to a cell.
--
HTH...

Jim Thomlinson

Hi,

Is there an easy way to use cell (or range) values in an MS Query SQL
- e.g. [select * from table where id = Cell("E4")]. TIA.- Hide quoted text -

- Show quoted text -

 

[Tim Williams]

They are limitations of that approach. If you need more flexibility you
could use ADO directly.

Tim


Thanks. A couple of issues:

1) Can't do [ ... WHERE col IN (?)] Only allows single values.

2) Can't have a parameter in a query that can't be displayed
graphically.

Are these for real?

Add a prompt to your query so that it becomes parameterized.

select * from table where id = ?

Now exit the query and return to XL. If you select Data -> Import External
Data -> Parameters you will be able to assign the parameter to a cell.
--
HTH...

Jim Thomlinson

Hi,

Is there an easy way to use cell (or range) values in an MS Query SQL
- e.g. [select * from table where id = Cell("E4")]. TIA.- Hide quoted
text -

- Show quoted text -

 

[ward376]

When you are editing the query using the interface (not the wizard)
use square brackets with a space in between to create parameters.
These will enter the ?s in the SQL statement while the interface
writes SQL for you.

When you return to Excel, you'll be prompted for the parameters. You
can choose ranges (cells) for the parameters and tell it whether to
always look there for each parameter.

Cliff Edwards