MS Query Parameter Prompt 1

[sxschech]

I used thread68-1143813 parameters are not allowed in queries that can't be
thread68-1268757 to figure out how to add a parameter to the SQL I copied from Oracle into MS-Excel's MS-Query. The tip worked. I would like to find out if it is possible to have a descriptive prompt rather than "Enter Parameter Value" and "Parameter 1".

Here is the original statement from Oracle

and aa.term = _term

Then in MS-Query I changed it to

and aa.term = '200920'

Next went to Script Editor and changed '200920' to ?


Thank you.

 

[SkipVought]

Hi,

Yes. IN the MS Query Editor, enter the propmt text you want to see within BRACKETS [Enter your-field-name-here value] in the Criteria Value, or something similar.

I prefer to use a cell on the sheet for the user to enter the value, and run the query when that cell changes, which is one option in the Data > Parameters window.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[sxschech]

Skip,

I had tried the bracket approach initially, when that didn't work, I did further searching to find the threads I mentioned. When I tried the bracket, I get the error [Microsoft[ODBC driver for Oracle][Oracle]ORA-00936: missing expression.

 

[SkipVought]

Please post your SQL, including your parameter prompt.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[sxschech]

I was hoping it wasn't necessary to include the entire SQL as it is quite lengthy, includes several subqueries and works fine when the value is hard coded or substituted with the question mark (?) prompt as shown in my original post. Restated below...

---------------------------
Original statement from Oracle looks like this

and aa.term = _term

---------------------------
In MS-Query I modified the prompt statement to this

and aa.term = ?

which provides the result I need (except for the non friendly parameter prompt input window)
---------------------------

In the meantime, I am utilizing your suggestion of referring directly to a cell for the parameter. I have now added a sheet into the file to hold the parameter and ticked "Use this value/reference for future refreshes. If I continue with this approach, how can I adjust the prompt's location in the future as the parameter window no longer shows up. For example, I have it in a Sheet Named "Parameters" cell B1. Perhaps the user wants it in A2 instead or perhaps wants it on the tab where the data are so that he doesn't need to flip between tabs. How would I bring back or edit the parameter cell location?

 

[SkipVought]

Perhaps the user wants it in A2 instead or perhaps wants it on the tab where the data are so that he doesn't need to flip between tabs. How would I bring back or edit the parameter cell location?

YOU can set it up so that the user can enter the data in a specifiec cell other than the "parameter cell" or on another tab. You just reference that cell in the "paramter cell" HOWEVER, you loose the fact that that cell is not changing, so the value that is entered somewhere else never triggers the query to run.

There are ways to overcome this problem, but it will take VBA code, using the worksheet change event. If you want to pursue this option, please repost in Forum707, along with the code that you will generate from these instructions...
[tt]
1. Activate the sheet containing your query table results.

2. Select any cell within the query results.

3. Turn on your macro recorder.

4. Data > Import External Data > Edit Query

5. activate the Query Editor

6. File > Return data to Microsoft Excel
[/tt]
Please post the recorded code in Forum707 along with an explanation of your problem.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[sxschech]

Hi Skip,

I found the menu to change the prompts and cell reference. Previously it didn't show up. I can't recall whether it was grayed out or because menu wasn't expanded? After Excel crashed, I reopened the file and went to the Data menu, under Import External Data, I found the Parameters menu. I see where I can not only add a user friendly prompt, but also can change the cell location for cell based parameters. So looks like no need to do the vba thread. Thanks for your help.