Problem with MS Query criteria

[Denise]

I am trying to create a query that uses an ODBC connection
to our Oracle database and returns a specific piece of
data based on a criteria value stored in a cell on the
worksheet. In other words, we need to look up a range of
values and return related values from the database. We've
managed to get all data from the database but we cannot
determine how to use excel data as the criteria. Any
suggestions?

 

[Tom Ogilvy]

if your using code, in your query string, concatenate your range values.

if the query string were

"Select dog from Table where address_no = 123"

"Select dog from Table where address_no = " &
worksheets(1).Range("A1").Value

 

[Denise]

We're using the Microsoft Query functionality instead of
using script. I've tried to use the worksheets(1).Range
("B2").Value option in the SQL window of Query but keep
getting a "missing right parenthesis" notification (I've
counted...it's not missing). Other ideas?

 

[Tom Ogilvy]

put the query in the spreadsheet using Get=>External Data.

After you have the data in the sheet, right click on the data and choose
edit query

This should give you the ODBC layout screen.

go to the menu and select criteria and add a criteria.

Choose a field and for value put in a Question Mark.

If you have continuous refresh on, it query you for a value. Put in any
valid value.

this should put the criteria there with a value of [] (paired brackets).
this makes it a parameter query.

Return to Excel.

Now Refresh the query and when you are prompted for the value, click in the
box and then click in a worksheet cell where you have your value. the box
should then show something like =Sheet1!B9
You may have to click the little checkbox below the box with the formula to
tell it to always use this value when it refreshes.

You can also right click in the data and select parameter from the menu.
You can put the above formula in the bottom box in the popup if it isn't
already there.

There are probably other ways you can get this done, playing around with the
options, but this did work for me. If I changed the value in Cell B9 and
refreshed the query, it used that value.

 

[Denise]

Tom,
Thank you for all the advice. That worked for me as
well. I was able to query up data using the value in that
one cell. This will be useful for us in a number of
ways. Unfortunately, I was hoping to be able to use a
range of cells (like B2:B20) using the equivalent of a SQL
IN operator (like "where dog_id in (1,2,3,4)"). It
doesn't look like that works with the parameter function.
I think we're going to circumvent this problem and load
the values in the excel spreadsheet into a table in Oracle
to do the lookup.

-----Original Message-----
put the query in the spreadsheet using Get=>External Data.

After you have the data in the sheet, right click on the data and choose
edit query

This should give you the ODBC layout screen.

go to the menu and select criteria and add a criteria.

Choose a field and for value put in a Question Mark.

If you have continuous refresh on, it query you for a value. Put in any
valid value.

this should put the criteria there with a value of [] (paired brackets).
this makes it a parameter query.

Return to Excel.

Now Refresh the query and when you are prompted for the value, click in the
box and then click in a worksheet cell where you have your value. the box
should then show something like =Sheet1!B9
You may have to click the little checkbox below the box with the formula to
tell it to always use this value when it refreshes.

You can also right click in the data and select parameter from the menu.
You can put the above formula in the bottom box in the popup if it isn't
already there.

There are probably other ways you can get this done, playing around with the
options, but this did work for me. If I changed the value in Cell B9 and
refreshed the query, it used that value.

--
Regards,
Tom Ogilvy

We're using the Microsoft Query functionality instead of
using script. I've tried to use the worksheets(1).Range
("B2").Value option in the SQL window of Query but keep
getting a "missing right parenthesis" notification (I've
counted...it's not missing). Other ideas?

.