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

How to Use Prompted Date Ranges in MS Access 2007 using Vista

Create a Link to Table

     Let's start from the beginning. Open Microsoft Access 2007 -> Create a Blank Database:

     Using the External Data tab -> use the More icon and select: ODBC Database

     Select the 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 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 :

Define your QuerQueryh the Link Table

     Using the Create tab, click on the Query Design icon and 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 :

Define your SQL Statement

     Back in the Query pane, click on the SQL icon, and the entered parameters are shown.

     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 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 querQueryve the design :

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

     Then Enter Finish Date :

And the querQueryl then runs and returns the Invoice Lines for the date range selected:

 

Questions and Answers

Question

     By any chance, have you tested this with office 2007 on XP SP 2? Access keeps crashing (closing with no error msg) when I select the table I want to link. I can import tables into Excel but need to connect them to update large amounts of customer data I have on my payment gateways. I have also tried Qintegrator as I need to update the customer -> currency fields. Qintegrator does not allow me to do this.

     I am guessing this error can be caused by two reasons; the odbc is returning non-supported data to access or Jet. OR Access 2007 does not work on XP with your driver.

Systems info:   QuickBooks Pro 2005 UK edition / MS Access 2007 (with office pro) / XP Service Pack 2 / MS Jet up to date / ODBC connected and working

Answer

     I cannot do that combination because QuickBooks 2005 and 2006 UK, 2006 and 2007 Canadian, and 2006/07 and 2007/08 Australian editions don't support Microsoft Office 2007. As far as QuickBooks and Windows XP SP2 is concerned, only Microsoft Office 2003 should be used.

     Make sure you use QODBC v7.00.00.214 (or higher) and start a new database. I've seen MS Access close when I tried to add additional linked tables to existing projects and had no trouble adding them to new projects.

(122 vote(s))
Helpful
Not helpful

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