I'm trying to export the general ledger data using various sp reports.  I have found that for account numbers that are "buried" within other account numbers and are missing an account number, the sp reports do not retrieve the AccountListID.

When I type in an account number in the chart of accounts, the sp reports work.  However, I have almost 700 g/l account numbers that need to be edited.

Is there a way around this?

Try just doing:

Select * from Account

Hi Tom,

My problem isn't with the 'Account' table.  Its with the sp_report TxnDetailByAccount or General Ledger report

Here is my query: sp_report TxnDetailByAccount show TxnId, AccountListID,AccountNumber, Date,TxnType, PoNumber, RefNumber, Name, Memo, Amount  parameters DateMacro = 'ThisYear'  where TxnType is not null

I think you are referring to sub-accounts, but without a specific example it's hard to tell. System transactions, however don't have a AccountListID:-

Yes, I'm referring to subaccounts.  In the chart of accounts which I am using, some accounts are "buried" several layers deep.  It seems that when it is buried to far, the 'sp_reports' do not report the AccountListID.  If I add a number to the account, then 'sp_reports' works.  As you can tell from the example below, the transactions are 'Bill's.  On a side note, I don't know this for a fact, but I would think every QuickBooks account has a AccountListID.

I could add the numbers to the sub accounts, but there are over 500 accounts which would have to be modified.

Tom,

Thanks for the update.

I think when compared to developing an alternative to the SP_REPORT and modifying the account records, the modification is the better route.