Excel 2007 data query parameter issue

Discussion in 'Microsoft Excel Programming' started by Steve Braswell, May 22, 2008.

  1. 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?
     
    Steve Braswell, May 22, 2008
    #1
    1. Advertisements

  2. 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/excel/HA100996641033.aspx?pid=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/subscriptions/managednewsgroups/default.aspx#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/subscriptions/support/default.aspx.
    ==================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.


    "Steve Braswell" <> wrote in message
    news:...
    >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?
    >
    >
     
    Jialiang Ge [MSFT], May 23, 2008
    #2
    1. Advertisements

  3. Hello Steve,

    I am writing to check the status of the issue on your side. Would you mind
    letting me know the result of the suggestions? If you need further
    assistance, feel free to let me know. I will be more than happy to be of
    assistance.

    Have a great 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:
    .

    This posting is provided "AS IS" with no warranties, and confers no rights.
    =================================================
     
    Jialiang Ge [MSFT], May 27, 2008
    #3
  4. Hi I have followed your help to create a parameter in query wizard. Yes
    it is true, once that I add the [param] the parameters option is finally
    available. Unfortunately, I am working in Excel 2007, I can't do
    anything with that.
    I open it, I can see my param available, but the Edit Button is greyed
    out.
    In many blogs I was a reference to the External Data Toolbar, where I
    should find the magic option to link a parameter to a cell. But I am
    afraid, I can't find it anywhere in my Excel. Where is it? How do I
    enable it?
    I am really frustrated by this issue. Apparently, lots of people are
    going through the same problem, given that the Microsoft Help query is a
    bit rubbish on this regard. But all the help I can find sends to how to
    write a web parame query, that means writing an ASP query with my
    parameter. But I don't want to do that, I simply need to link my query
    parameter to a cell. It should be so simple to achieve and so clear in
    the help. Why is Microsoft sometimes so tricky?
    please any help would be welcome.
    Nicoletta

    *** Sent via Developersdex http://www.developersdex.com ***
     
    nicoletta landi, May 6, 2009
    #4
  5. I'm having exactly the same problems with 2007, not much help, but at
    least you know your not alone.

    *** Sent via Developersdex http://www.developersdex.com ***
     
    Mitchell Pearce, Jul 23, 2009
    #5
  6. Steve Braswell

    Heather Guest

    Heather, Oct 22, 2009
    #6
  7. oded ben-porat, Mar 27, 2010
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. CodeMonkey

    Use ADO to retrieve data from Access Parameter Query

    CodeMonkey, Dec 16, 2004, in forum: Microsoft Excel Programming
    Replies:
    3
    Views:
    302
    Guest
    Dec 17, 2004
  2. Guest

    Cannot get the parameter option to work in Excel query

    Guest, Sep 27, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    206
    Guest
    Sep 30, 2005
  3. Newbrier

    Excel DB Query Parameter for all values

    Newbrier, Mar 6, 2008, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    212
    Newbrier
    Mar 6, 2008
  4. AFSSkier

    What is the wild card for an Access query (MS query) parameter?

    AFSSkier, Apr 1, 2009, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    2,337
    AFSSkier
    Apr 1, 2009
  5. Koppite412

    Parameter Query Refresh issue

    Koppite412, Aug 19, 2009, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    177
    Koppite412
    Aug 19, 2009
Loading...

Share This Page