Solved

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

Posted on 2013-10-25
2
344 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now