Solved

How to pass criterion from Excel to SQL View to return only desired result set to Excel

Posted on 2013-10-25
2
374 Views
Last Modified: 2013-10-25
Hey experts,

We are looking for a way to pass criterion from an Excel spreadsheet via an ODBC connection to an SQL view in order to return the select set of records we specify in the criterion. We can't specify this criterion in the SQL view because the user will want to change the criteria everytime they run the view by keying their own criterion - an unchanging set of fields that they key values for - e.g. customer name, customer ID, etc., into specified cells on the spreadsheet. The goal would be to have the criterion passed to the view which would then return the matching result set back to the spreadsheet for formatting and display to the user.

We have already established an ODBC connection which returned the result set for an existing view. We now need to put in place the piece which allows the user to specify the criterion to narrow the results that are returned.

Appreciate any help that you can provide us.

TaDUH
0
Comment
Question by:taduh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39601462
As I understand, you want to be able to type a value in a cell in Excel, and that value passed as a criterion to the query.
This is how to do it.
1. Select the table, right click, select table and Edit.
2. Set the criteria you want.
3. At some point you can desire to return the table, or edit in MSQuery, select MSQuery.
4. Then change the value in the criteria to [Parameter].
5. Use a meaningful name (don't use a field name) for Parameter in the [ ] brackets.
6. Hit Enter, and you will be asked for the criterion value.
7. Return the table to Excel (Menu Files).
8. Select the table, right click, select Parameters, attach the parameter to a cell, mark for update when cell change.

Then change the cell value, and the table will update.
You can use more than one parameter.
0
 

Author Closing Comment

by:taduh
ID: 39601719
Right On! Thank you so much!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question