How to create an Excel query that allows variable criteria inputs

Posted on 2010-01-02
Last Modified: 2013-11-28
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?
Question by:Delivery
    LVL 10

    Accepted Solution

    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.

    Author Comment

    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.
    LVL 19

    Assisted Solution

    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.

    Author Comment

    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.


    Author Closing Comment

    Thank you both.

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


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now