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
Solved

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

Posted on 2013-10-25
2
367 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
2 Comments
 
LVL 21

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

789 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