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

Define your own Query with 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 Enter Start Date and Enter Finish Date as shown and click OK :

Define your own SQL Statement

     Back in 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:

 

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 link them to update large amounts of customer data I have on my payment gateways. I have also tried Qintegrator with no avail as I need to update customer -> currency fields, Qintegrator does not allow me to do this.

     I am guessing this error can be caused by one of 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 all 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 are using 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 yet had no troubles 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).