PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

Excel 2007 data query parameter issue

 
 
Steve Braswell
Guest
Posts: n/a
 
      22nd May 2008
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?


 
Reply With Quote
 
 
 
 
Jialiang Ge [MSFT]
Guest
Posts: n/a
 
      23rd May 2008
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 ((E-Mail Removed), 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:
(E-Mail Removed).

==================================================
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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?
>
>



 
Reply With Quote
 
 
 
 
Jialiang Ge [MSFT]
Guest
Posts: n/a
 
      27th May 2008
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 ((E-Mail Removed), 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:
(E-Mail Removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 
Reply With Quote
 
nicoletta landi
Guest
Posts: n/a
 
      6th May 2009


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 ***
 
Reply With Quote
 
Mitchell Pearce
Guest
Posts: n/a
 
      23rd Jul 2009


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 ***
 
Reply With Quote
 
Heather
Guest
Posts: n/a
 
      22nd Oct 2009
Hi All
I got this working - though the MS help didn't help much.
Found the solution here:
http://www.mrexcel.com/forum/showthread.php?t=78051

Just put ? in the query where you need it to be a prompting paramater.

Hope this helps.
Heather



*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
oded ben-porat
Guest
Posts: n/a
 
      27th Mar 2010




*** Sent via Developersdex http://www.developersdex.com ***
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameter Query with Parameter value(s) or no entry returns all =?Utf-8?B?QmV0dGVyZ2FpbnM=?= Microsoft Access 3 17th Aug 2011 04:33 PM
Excel 2007 data query parameter issue Steve Braswell Microsoft Excel Discussion 1 8th Sep 2008 08:33 PM
Pass a parameter to a parameter query from a data access page =?Utf-8?B?TWF0dA==?= Microsoft Access Queries 10 16th Aug 2006 02:52 PM
Pass parameter to a parameter query from a data access page =?Utf-8?B?TWF0dA==?= Microsoft Access 0 11th Aug 2006 06:18 PM
error STOP:0x0000007B (parameter, parameter, parameter, parameter) robert35 Microsoft Access Getting Started 1 15th Dec 2004 04:28 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:07 PM.