?
Solved

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

Posted on 2013-10-25
2
Medium Priority
?
380 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 23

Accepted Solution

by:
Ejgil Hedegaard earned 2000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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…

765 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