Let's start from the beginning. Open Microsoft Access 2007 and create a Blank Database:
Using the External Data tab, use the More icon and select: ODBC Database
Select Link to the data source by creating a linked table :
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 2007 to access your QuickBooks data. Check the "Yes, always; allow acess even if QuickBooks is not running" 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 :
Using the Create tab, click on the Query Design icon and select the linked InvoiceLine table :
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 on the Query pane click on the SQL icon, the parameters that were entered are shown.
You can now simply paste in your own 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 the Start Date for Invoices you want to see] And (InvoiceLine.TxnDate) <= [Enter the Ending Date for Invoices you want to see]));
Click on the [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:
|