how do I add paramters / criteria in the excel query?
 

I have created an ODBC link to upfdate excel from Oracle. I ned to know how
do I add paramteres in the query so that the user enters the paramter. '@' in
the query throws an error

how do I add paramters / criteria in the excel query?
 

I assume you set up the ODBC link using MSQuery? Setting up parameters is
pretty easy, and you have 3 choices when you do: Use a fixed value (prob not
of interest for you here!), prompt user at time query updates, or use a cell
value from your spreadsheet.
In MSQuery: parameters are indicated by using a name within square braces,
[], within the criteria grid. For example, using a data column for the
selection criteria I could type in the grid the following:
Between [Start date] and [End date]
Now if I refresh the query inside MSQuery, it will prompt me for Start date
and End date. It will also prompt when you return the data to Excel - don't
worry about the values at this point, you can use anything because later you
will set up the values in Excel.
Back in Excel now, right-click somewhere within the result range of the
query. The popup menu should have an option for "Parameters..." and when you
select this you will see a dialog come up. Your parameters (Start date and
End date in my example) will be listed and when you click on one to select it
you can choose how to supply the value (the 3 choices I mentioned above -
fixed value, prompt at runtime, or use a cell value - you specify which cell
from within this dialog).
That should do it, I hope.

BTW: '@' does not work since ODBC needs to use SQL to send the query command
to your database, so you cannot use all the native database conventions, only
SQL. But setting up the query as above builds the SQL for you automatically
so you don't need to worry about syntax.
--
- K Dales


"Mary" wrote:

I have created an ODBC link to upfdate excel from Oracle. I ned to know how
do I add paramteres in the query so that the user enters the paramter. '@' in
the query throws an error

how do I add paramters / criteria in the excel query?
 

Hi Dales,
Thank you very much for your detailed reply.
that was a good help. I needed to prune my query from Pl/SQL to native SQL.
Also, I guess since the query was a big one with severeal tables and views,
MS Query wasnt able to represent it graphically, and thus not allow
parameters.
thanks again

Mary

"K Dales" wrote:

I assume you set up the ODBC link using MSQuery? Setting up parameters is
pretty easy, and you have 3 choices when you do: Use a fixed value (prob not
of interest for you here!), prompt user at time query updates, or use a cell
value from your spreadsheet.
In MSQuery: parameters are indicated by using a name within square braces,
[], within the criteria grid. For example, using a data column for the
selection criteria I could type in the grid the following:
Between [Start date] and [End date]
Now if I refresh the query inside MSQuery, it will prompt me for Start date
and End date. It will also prompt when you return the data to Excel - don't
worry about the values at this point, you can use anything because later you
will set up the values in Excel.
Back in Excel now, right-click somewhere within the result range of the
query. The popup menu should have an option for "Parameters..." and when you
select this you will see a dialog come up. Your parameters (Start date and
End date in my example) will be listed and when you click on one to select it
you can choose how to supply the value (the 3 choices I mentioned above -
fixed value, prompt at runtime, or use a cell value - you specify which cell
from within this dialog).
That should do it, I hope.

BTW: '@' does not work since ODBC needs to use SQL to send the query command
to your database, so you cannot use all the native database conventions, only
SQL. But setting up the query as above builds the SQL for you automatically
so you don't need to worry about syntax.
--
- K Dales


"Mary" wrote:

I have created an ODBC link to upfdate excel from Oracle. I ned to know how
do I add paramteres in the query so that the user enters the paramter. '@' in
the query throws an error