Excel 2003 send date parameters to SQL query

tags:

excel
sql

views:

answers:

+1 Q:

Excel 2003 send date parameters to SQL query

PROBLEM: I have a few worksheets that use sql queries to retrieve data. I would like all of these sheets to query data based on the same date range entered by a user in excel.

eg. I would enter somewhere in a form or in a cell a date range and retrieve the value in sql to obtain data based on the user specified dates.

Is there anyway to access values from forms or from cells in SQL (Query editor perhaps)?

Thanks!

In MS Query (which you use to build a QueryTable) set the tick to display the Criteria grid, and for required fields enter something in brackets as a criterion value:
A screenshot

This will create two parameters in the query. Save and return to Excel, right-click the QueryTable, click Parameters... and point them to the right cells.

GSerg 2010-07-20 23:35:33

Where is this "set the tick to display the Criteria grid"?thanks!

Ehsan 2010-07-20 23:48:17

`View -> Criteria`

GSerg 2010-07-21 10:17:06

I figured it was simple but I'm using Excel 2003 and I do not have Criteria enabled for some reason. What is the condition that might make this enabled. I have MS Query open, I'm looking at my table which I queried and most menu options are gray-ed out. Any thoughts?Thanks

Ehsan 2010-07-21 15:23:25

I opened MS Query by going to my sheet which has external data and I chose "Edit Query" from the Data menu and so most of my menu options are disabled.

Ehsan 2010-07-21 16:41:44

Do you get the `This query cannot be represented graphically` message by any chance? Parameters are not supported for such queries. And what is your data source?

GSerg 2010-07-21 16:54:29

Yea I get that message and I continue anyway. My data source is a sql database on my sql server. How do I write a query for excel so I don't get that message. Or rather, how do I write a query so I can use criteria on it.

Ehsan 2010-07-21 17:26:49

MS Query is quite crappy at building queries. You want either to make your query so simple that MS Query can represent it graphically (and therefore can allow parameters on it), or to move this query to the server by creating a view based on your `select` statement, and then using a mere `select * from this_view` in MS Query (which obviosuly will allow parameters). Don't worry about the fact that the view will return all records without filtering -- SQL Server is smart enough to apply the parameters from Excel before executing the `select`.

GSerg 2010-07-21 18:28:10

Ah ok thanks that cleared it up well, I'm doing a complex query so that explains why I can't do it graphically. Thanks GSerg, great info.

Ehsan 2010-07-21 19:49:54

+2 A:

I think this example might be what you are looking for?

Parameters in Excel external data queries

MrUpsideDown 2010-07-20 23:38:27

ansaurus

tags:

views:

answers:

Excel 2003 send date parameters to SQL query

related questions