Get External Data Query

  • Thread starter Thread starter drswanker
  • Start date Start date
D

drswanker

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!!!
 
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!!!


------------------------------------------------

~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step
Click to expand...
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.

drswanker said:
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!!!


------------------------------------------------



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