I would like to run a query across an external db using a value from a
cell as a restriction in the query.

I would like the query to do something like:

SELECT DCMS.NAME
FROM RXC.DCMS DCMS
WHERE (DCMS.DOMAIN='E$1$')

The query attempts to look only for possible values in the external db.
I need to restrict the name values to only the values which exist in
the domain indicated in cell E1.

I'm guessing I can't do this or will need to do it in the vba editor
rather than query builder?

Can anyone help?

Thanks!!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

You have to variablize the value. I am assuming you are
executing the query as a string query. Do this:

"SELECT DCMS.NAME " _
& "FROM RXC.DCMS DCMS " _
& "WHERE (DCMS.DOMAIN='" _
& Range("A1") _
& "')



-----Original Message-----

I would like to run a query across an external db using a
value from a
cell as a restriction in the query.

I would like the query to do something like:

SELECT DCMS.NAME
FROM RXC.DCMS DCMS
WHERE (DCMS.DOMAIN='E$1$')

The query attempts to look only for possible values in
the external db.
I need to restrict the name values to only the values
which exist in
the domain indicated in cell E1.

I'm guessing I can't do this or will need to do it in the
vba editor
rather than query builder?

Can anyone help?

Thanks!!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
.

drs

Open the query in MSQuery (from the last page of the query wizard) and
within MSQuery make it a parameter query. To do that, add criteria under
the Domain filed and put it in brackets, like this

[Enter domain]

This will create a prompt when you refresh the query. Now, return to Excel
and on the External Data commandbar, click the parameters button. Here you
can change this from a prompt query to read the value from a cell.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"drs******" wrote in message
...

I would like to run a query across an external db using a value from a
cell as a restriction in the query.

I would like the query to do something like:

SELECT DCMS.NAME
FROM RXC.DCMS DCMS
WHERE (DCMS.DOMAIN='E$1$')

The query attempts to look only for possible values in the external db.
I need to restrict the name values to only the values which exist in
the domain indicated in cell E1.

I'm guessing I can't do this or will need to do it in the vba editor
rather than query builder?

Can anyone help?

Thanks!!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements