Solved

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

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

803 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