Data Connections > SQL Query > Use Excel Range to filter

**smith_ts** · 10-22-2015, 10:30 AM

Hi Everyone,

Apologies in advance if my terminology is poor at any point.
This might explain why my Google searches for the answer have proved fruitless.

My Excel doc is pointing to an Access database for its data connection.
I have a simple SQL query running in this data connection...

Select * from row WHERE YEAR([Date]) = 2015 OR (YEAR([Date]) = 2014 AND MONTH([Date]) = 12 OR MONTH([Date]) = 11) order by ID asc

Now ideally what i would like to do is use cells in my Excel spread sheet instead of hard coding them into the SQL query.

My Access database has the headers:
Name Date TimeSpent Reason Allowance Supplier AllowanceDate Activity Tier AdsCompleted Country QuoteNo Comments ID

I would like to only return the data for entries in the database where the name appears in a range of cells.

So the SQL brings back any data relating to names in a certain cell range.

A B
1 Tom Good
2 **** Bad
3 Harry Ugly

Does that all make sense?

**GeneralDisarray** · 10-22-2015, 10:50 AM

So - you want to dynamically add a WHERE clause to your SQL statement based on the sheet? Is that correct?

Edit:

NOTE: This will only work if you edit my code to fit your project

This is the general technique I've used in the past for this same situation. The key is, you can access the Command Text of your query table programmatically.

It looks to me that you are looking to add a clause like: WHERE Name IN ('Tom','Harry','Jane'). To your SQL statement.

The way I do this, is to use VBA to re-author the query as needed.

Your query sits in a table object in the workbook, and it has a specific name -- assume that name is T_People. You need a macro to alter the command text as needed.

Something like:

Please Login or Register  to view this content.

Then you just need to decide what launches this (a button? a worksheet event? a workbook event?)...

Data Connections > SQL Query > Use Excel Range to filter

LinkBack

Thread Tools

Rate This Thread

Display

Data Connections > SQL Query > Use Excel Range to filter

Re: Data Connections > SQL Query > Use Excel Range to filter

Thread Information

Users Browsing this Thread

Similar Threads

SQL connections, wait with the code until query is done!

Dynamic source data in QUERY connections

Excel - web query error with some ISP Wifi connections

[SOLVED] Data Connections: Parameterized Query of Access DB... Is it possible?

Data connections have been disabled message - but no data connections?

Run Away Data Connections / Query?

Connections to Access DB - query availability

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions