[QODBC-Desktop] How to Use Prompted Date Ranges in MS Access
Posted by brad waddell on 12 March 2009 05:21 PM

How to Use Prompted Date Ranges in MS Access

Create Link Table

     Let's start from the beginning. Open MS Access -> Create a blank database -> Using the File menu pulldown -> Get External Data -> select Link Tables...

     Select Files of type: ODBC Databases ()

     Select Machine Data Source and QuickBooks Data :

QuickBooks Integrated Applications with Certificate

     When Microsoft Access starts QODBC and connects to a company file for the first time, QuickBooks will ask you permission for Microsoft Access to access your QuickBooks data. Check the Yes, always; allow access even if QuickBooks is not running the option. Your FLEXquarters QODBC application will be added to the Integrated Application list in your company file. Click on Continue.... to continue.

     QuickBooks will then confirm access; click on Done to continue.

     Select the InvoiceLine table used in this example :

Create a Query using the Linked Table

     Select the Queries tab and Create Query in the Design Mode view :

     Select the linked InvoiceLine table :

Setup Date Parameters

     Move your mouse to the main pane and right click and select Parameters... :

     Enter the PARAMETERS for entering Start Date and Enter Finish Date as shown and click OK:

     Back to the central pane right, click and select SQL View... :

     The parameters that were entered are displayed.

Define Your SQL Query with Date Parameter

     You can now paste in your SQL statement using the parameters (enclosed in [ ]), for example:

SELECT InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName,
InvoiceLine.TxnDate, InvoiceLine.RefNumber, InvoiceLine.InvoiceLineItemRefFullName,
InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineAmount
FROM InvoiceLine
WHERE (((InvoiceLine.TxnDate) >= [Enter Start Date] And (InvoiceLine.TxnDate) <= [Enter Finish Date]));

     Click on the red X to close the Query. Save the design :

     When the Query is run either as a query or a report, it will now prompt you to Enter the Start Date :

     Then Enter Finish Date :

And the Query will then run and return the Invoice Lines for the date range selected:

(140 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).