I am having trouble doing a simple query using Enterprise 7.0 and Excel 2007.  I go into Excel 2007 and select the data tab, then from other sources and finally Microsoft Querry from which I select QuickBooks Data.  Then I attempt to identify a schema that will allow me to enter into Excell a date range an account number and return simple data.  Have not found a way to access account numbers via ODBC.  Any ideas form anyone??

Thanks for any help,

Tommie Head

Port Orange, FL 

Tom, thanks for the heads up.  I am ok with date range, my real issue is to select by account number.  I should have worded my request different.  Let's say that account "6205" is usede to captur "Computer Expense."  What I want to do is put in the date range and the account number "6205"and get a return with; Expense name "Computer Supplies, vendor name "Joe Blow Bit Didlers", memo "2GB DDR Ram for Sue's PC", and amount "$147.96".  Seems like I can't get past the account number.  I have tried various schemas but can't get the expense account number.

Tommie

The account table only holds the Chart of Accounts. In QuickBooks there's no General Ledger Transactions table (GLTRANS) like other accounting systems. All the transactions are within the subledgers with account references.

The closest thing to a General Ledger Transactions table is the CustomTxnDetail report, see: How do I run a CustomTxnDetail Report? 

Try:

sp_report CustomTxnDetail show Text, Blank, TxnType, Date, RefNumber, Name, Memo,
AccountNumber, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance
parameters DateMacro = 'ThisMonthToDate', SummarizeRowsBy = 'TotalOnly',
OpenBalanceAsOf = 'Today' where AccountNumber = '6205'

Tom, having trouble with date format..  Regardles to format I use I continue to receive a conversion error.  This is my syntex          >=[Enter Start Date] And <=[Enter Finish Date]

I am using "TimeCreated"

I can't see a problem using:

>=[Enter Start Date] And <=[Enter Finish Date]

with the Account table.

Note: There's no TimeCreated column in sp_report CustomTxnDetail. Date ranges in reports are done either using the DateMacro parameter or DateFrom and DateTo, for example:-

sp_report CustomTxnDetail show TxnType, Date, RefNumber, Name, Memo,
AccountNumber, Account, ClearedStatus, SplitAccount, Debit, Credit, RunningBalance
parameters DateFrom = {d'2007-01-01'}, DateTo= {d'2008-01-01'}, SummarizeRowsBy = 'TotalOnly',
OpenBalanceAsOf = 'Today' where AccountNumber = '2000'

See: How do I use the QuickBooks Reporting Engine with QODBC? I've heard something about sp_report ? for further information.
BTW: You can't use prompted dates in MS Query for queries that can't be visually displayed.