[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 and 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 by this example :
Create Query using the Linked Table
Select the Queries tab and Create Query in 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 Enter Start Date and Enter Finish Date as shown and click OK:
Back to the main pane right click and select SQL View... :
The parameters that were entered are shown.
Define Your Own SQL Query with Date Parameter
You can now simply paste in your own SQL statement using the parameters (enclosed in [ ]), for example:
SELECT InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName,
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 Start Date :
Then Enter Finish Date :
and the query will then run and return the Invoice Lines for the date range selected: