How to Use Prompted Date Ranges in MS Excel

New Database Query

     Let's start from the beginning. Open MS Excel -> Using the Data menu pulldown -> Import External Data -> select New Database Query...

Locate Table and Field Record

     Select Databases and QuickBooks Data:

     Select the InvoiceLine table and the fields/columns as used in this example:

Setup Date Filter Parameter

     Enter the filter parameters for the TxnDate as shown:

     Click Next to bypass Sort by (Order by), as you should always allow QODBC to sort how rows are returned wherever possible:

     Click on View data or edit query in Microsoft Query and select Finish:

Setup Criteria Value for TxnDate

     Replace the Criteria value for TxnDate from:

>=#1/1/2006# And <=#1/1/2008#

     To:

>=[Enter Start Date] And <=[Enter Finish Date]

Date Data Type Related

Note: Due to Date, DateTime (used by MS Access), and Timestamp (used by MS Query) data types, when using MS Query, you will also need to change the Criteria Field from TxnDate (Date) to TimeCreated (Timestamp):

Return Data to Excel

     Using the File menu pulldown, select Return Data to Microsoft Office Excel... :

     You can select the cell where you want to put the data in your worksheet:

     Now, whenever you Refresh Data:

It will now prompt you to Enter the Start Date:

Then Enter Finish Date:

Questions and Answers

Question 1

     Is it possible to have a criteria sheet where the user can enter a Beginning and Ending date in the worksheet and have the query look to those cells to get the date ranges for the query? In other workbooks that I set up, I like to create criteria pages to allow the user to change aspects of the workbook and assign named ranges to those cells. That way, the data can be referenced throughout the entire workbook.

Answer 1

     I don't have an MS Excel example but see: Using QuickBooks Data with VBA for more on using VBA to program date entry and execution using MS Access.