Parameterquery - Button is missing

  • Thread starter Thread starter Harald Battran
  • Start date Start date
H

Harald Battran

Hello,

sorry I might use the wrong english terms as I am using the German
version of Excel 2007.

I made a query with MS Query from one Excel-table to another Excel-
table.

I would now like to parameterize the query with contents of fields.
Just as the Online help tells me can be done.

Therefore I should place the cursor somewhere in the result-table.
Then I should go to the data-ribbon and choose the setting-button. In
the dialog I should klick on "connection settings" which I did. On the
"definition" tab there should be a Button "Parameter...".

But this button is greyed out and I don't know why!

Can not set the filter without that button it seems.

Can anyone tell me what I am doing wrong or what I am missing?

Thanks a lot
Harald
 
Hi,

Please tell us the steps you went through to create the connection. When
you say "table" do you mean an Excel table created using the Format as Table
command or are you talking about a pivot table?

Cheers,
Shane
 
Hallo Shane,

first: I got a hint from the german newsgroup and now I have a work
around. It seems that the problem only exist in 2007.
Please tell us the steps you went through to create the connection.  When
you say "table" do you mean an Excel table created using the Format as Table
command or are you talking about a pivot table?
Click to expand...

I had a excel table (just normal row and columns) and wanted to create
another table with selection criteria via MS Query placed on in
another Excel-file. This worked just fine. Then I wanted to make the
Query more flexible and found in the online-help the description how
to base a MS-Query query on field contents. But as I described the
"Parameter" button is greyed out.

The workaround is that I go in the resulting Query-table. Select at
least one field. Go for the settings in the data ribbon, click on the
button connection-settings, click the tab "Definition" and click on
the button (left of the greyed out Parameter-button) for "change
query". The dialog which is poping up I cancel and the question if I
want to work with MS-Query to change the query I answer with "YES".

MS Query comes up and if not already done I add a criteria. (Menu
criteria - add criteria). In the "value-field" I write in i.e.
[para_name] (the brackets are important). MS Query then asks for a
value and you have to enter a valid value here.

After you have done this you can "return the values" and the
"Parameter..." button is active, so you can do the settings and
possibly use a field in the spreadsheet to alter the query result.

I hope this description helps someone who runs into the same
difficulties.

But if it is not a bug and I am doing something wrong, please do not
hestitate to tell me!

Thanks.

Harald Battran
 
Back
Top