ExcelBanter - Excel 2007 data query parameter issue

Good morning, Steve. Welcome to Microsoft Newsgroup Support Service! My name
is Jialiang Ge [MSFT]. I'm sorry to see the "Parameters" button is
frustrating you. I will help you out.

The "Parameters." button is enabled only when there are parameters defined
in the Microsoft Query, so our question is: how to add a parameter to the
Microsoft Query?

There are basically two options to define a parameter query:

(Prerequisite: I set up a data source for a SQL server DB by using Microsoft
Query: Data Ribbon-Get External Data-From Other Sources-From Microsoft
Query-Databases-New Data Source.)

*Option1. Use the Query Wizard*

Step1. Click on the "Edit Query." button in the "Connection Properties"-"Definition"
tab. This will start up the Query Wizard by default, if it does not popup
the message "This query cannot be edited by the Query Wizard".
Step2. Click on "Next" in the "Query Wizard" dialog. You will be guided to
"Query Wizard-Filter Data", in which columns to filter are listed on the
left side. Select a column, and on the right side, choose an operator, e.g.
"equal", and type ? or [ (a left square bracket) followed by the text for
the prompt you want Microsoft Query to display when the query is run, and
then type ] (a right square bracket) next to it. Click "Next", "Next",
"Finish". A dialog will be popped up for the default value of the parameter.
Step3. Now, you will see the "Parameters." button is enabled.

*Option2. Use the Microsoft Query utility*

The second option is to use the Microsoft Query utility. It can be started
in at least three ways:
A. Click on the "Edit Query." (see Option 1). If a message box: "This query
cannot be edited by the Query Wizard" is popped up, Microsoft Query utility
will be started after then.
B. Click on the "Edit Query." (see Option 1). If the Query wizard is started
successfully, please go to the last step of the wizard, where you will see a
radio box "View data or edit query in Microsoft Query". Choosing it and
clicking Finish will bring up the Microsoft Query utility.
C. On the Data tab of Excel 2007, in the Get External Data group, click From
Other Sources, and then click From Microsoft Query. In the Choose Data
Source dialog box, make sure that the Use the Query Wizard to create/edit
queries check box is clear. Then double-click the data source that you want
to use.

After the Microsoft Query utility is started, we can add a parameter (also
named as 'Criteria') in this way:

1. Create a query that includes the tables and fields for the records you
want.
2. Make sure Auto Query is not pressed in.
3. Make sure Show/Hide Criteria is pressed in to display the Criteria pane.
4. Click the first cell in the Criteria Field row, and then click the arrow
in the cell to select a field from the list that you want to use as the
parameter for the query.
5. Click the first cell in the Value row. Type [ (a left square bracket)
followed by the text for the prompt you want Microsoft Query to display when
the query is run, and then type ] (a right square bracket). An example is
shown in the following illustration.

Criteria Field: CITY
Value: [Type the name of the city]

The text of the prompt must be different from the field name, although it
can include the field name.
To create more than one parameter for the query, repeat steps 4 and 5 for
each parameter you want to add in the next available column in the Criteria
pane.

6. Press ENTER.

After then, save the query, and you will see the "Parameters." is enabled.

Steve, please try the options above and let me know whether they help or
not. For more readings, please refer to the article:
http://office.microsoft.com/en-us/ex...CH100648471033
and the Help files accompanied with Microsoft Query/Query Wizard by clicking
the "Help" button in the dialogs.

Please let me know if you have any other concerns, or need anything else.
Have a nice day!

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
.

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

"Steve Braswell" wrote in message
...
I cannot add parameters to external data connection (SQL query) in Excel
2007.
I am trying to add an external data connection in Excel 2007. I create a
new
connection to my SQL Server. I change the properties to a SQL statement. I
embed a question mark (?) where I need parameters, but the "Parameters"
button remains grayed out and unavailable. The query fails with a "No
value
given for one or more parameters." Error.

This applies to queries against a stored procedure or table function.

Frustratingly, I have been occasionally successful in getting the
Parameters
button available. When I set up the parameters, the query works fine. But
I
cannot reproduce this behavior 90% of the time! Even within the same
spreadsheet against the same data source.

What do I need to do to make the parameters button available for queries
with parameters?