How to create an Excel query that allows variable criteria inputs

I would like to create a query in Excel that hits an Access database and always returns the results to a specific location on a page in an Excel spreadhseet but I need to be able to change the date range criteria at will.  Can the new data always overwrite the old, so I don't have to worry about overlap?
Can this be done?
Who is Participating?
FayazConnect With a Mentor Commented:
This could be done using the database connection option.  Accessing the funtion may vary depending which version of office/ excel in use.  If you have the sample database, upload it as well.
DeliveryAuthor Commented:
I was thinking of using the Query wizard to create the query and connect to the database, which I have done before.
But I only know how to create a query with fixed variables that I can edit.  I am hoping to have some cells in Excel that I can use as input ranges for the query, such as ">= date".  But, don't know how to strcture the query to allow this.

Don't have a database sample yet.
folderolConnect With a Mentor Commented:
If you are using a simple query select statement, then you can use the parameters option in MS Query to select cells to use for the date range.  One easy way to get started is to simply put

and MS Query will prompt with textboxes for the dates when it executes the query.  You can go to Data Connection properties to change the parameters from input prompts to cell contents.

For more complex queries, the = ? syntax is not permitted, you can test to see if you can use it, but if it generates an error message, your alternative is to use VBA code to modify the querytables(1).CommandText property.

The VBA is different for Excel 2003 and Excel 2007, so we need to know which version you are using to give you a code example.
DeliveryAuthor Commented:
I am using Excel 2003 & Access 2003.

I will try the format; WHERE STARTDATE >= ? and ENDDATE <=
If this works, it would be the best way for me to do this.

I am trying to avoid having to learn VBA code at the moment and trying to capitalize on my exerience with Excel capabilities.

Last issue on this question: How can I ensure that the result of the query always gets put in the same place (so Excel formulae will work)?  I recall that during query creation I can set the destination of the resulting data, but I can't seem to find how to modify this under Query Edit.

One other question for this early stage of development relates to the wisdom of the strategy of using Excel Queries vs Access Reports.  I will soon post a new quesiton specifically about this.

DeliveryAuthor Commented:
Thank you both.

I hav tested your suggestions.  They work great so far and I am on the road to creating my reports.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.