How would you delete SalesReceiptsLine records that show that the deposit status is still in Undeposited Funds account?  Our system, by default, associates sales receipt to the Undeposited Funds account.  After a period, our accountant then deposits the funds to an associated bank account.  Therefore, I only want to be able to selectively delete those sales receipt transactions which have NOT been deposited to a bank account.  Please reply soon.

The sales receipts were imported from an Ecommerce export tool.  There were problems which caused some duplicate records to appear ... too massive to reasonably remove by hand.  We are aware of the purpose of importing sales receipts instead of through the invoicing route.

Our system is set by default to deposit these receipts to the Undeposited Funds account.  Once the funds are confirmed/receonciled with the bank, they are then deposited via QB to the appropriate bank account.  There are some transactions in which this QB bank deposit function has not been performed.  Theyu remain in the Undeposited Funds account.  These are the transactions we desire to delete.

Ok, make sure you have deposited all the correct Sales Receipts into your bank accounts, then run:

SELECT * FROM salesreceipt where DepositToAccountRefFullname = 'Undeposited Funds'

using VB Demo. This will show all the "Undeposited" Sales Receipts that you have in your system. If you're happy that the Sales Receipts shown are all duplicates, then delete them all by doing:

DELETE FROM salesreceipt where DepositToAccountRefFullname = 'Undeposited Funds'

but I'm guessing it won't be as easy as that because undeposited sales receipts still show up as undeposited in QuickBooks even when they have been banked.

We have attempted this query, however, the SaleReceiptLine record still contains the value of 'UndepositedFunds' even after the record has been deposited to the bank charter account.  We verified this by looking at the journals.

We are assuming that there is another table join that will indicate if the transaction was truly deposited (moved) over to a journal bank account.  These are the transactions that we certainly do not want to delete.

Also, an additional criteria for our delete is to compare the date fields (e.g. txndate).  What is the format for this field when filtering? When we use "... where txndate >  2006-2-1..." we get an "...Invalid Operand for Operator..." message.

Please reply soon.  Thanks in advance.

The date syntax is:

SELECT * FROM salesreceipt where DepositToAccountRefFullname = 'Undeposited Funds'
and TxnDate >= {d'2006-02-01'} and TnxDate < {d'2006-07-01'}

As I'm guessing that you have defined date range when the Ecommerce transactions were duplicated.

To isolate all the Undeposited Sales Receipts that have not been deposited to a Bank Account we have to do a LEFT OUTER JOIN with the DepositLine table and find out if there's no bank deposit account like this:

SELECT salesreceipt.TxnID, salesreceipt.CustomerRefFullName,
salesreceipt.Refnumber, salesreceipt.DepositToAccountRefFullname,
salesreceipt.TotalAmount, DepositLine.DepositToAccountRefFullname
FROM {oj salesreceipt LEFT OUTER JOIN DepositLine
ON salesreceipt.TxnID = DepositLine.DepositLineTxnID}
Where salesreceipt.DepositToAccountRefFullname = 'Undeposited Funds'
and DepositLine.DepositToAccountRefFullname is NULL 

Got it!  Thanks much.

I am attepting to delete from this statement but get an error:
-------------------------------------

Delete FROM {oj salesreceipt LEFT OUTER JOIN DepositLine
ON salesreceipt.TxnID = DepositLine.DepositLineTxnID}
Where salesreceipt.DepositToAccountRefFullname = 'Undeposited Funds'
and DepositLine.DepositToAccountRefFullname is NULL

Sorry, you're going to have to do this the hard way. The delete query smarts would normally be:

Delete FROM salesreceipt where salesreceipt.TxnID =
(SELECT salesreceipt.TxnID
FROM {oj salesreceipt LEFT OUTER JOIN DepositLine
ON salesreceipt.TxnID = DepositLine.DepositLineTxnID}
Where salesreceipt.DepositToAccountRefFullname = 'Undeposited Funds'
and DepositLine.DepositToAccountRefFullname is NULL)

but QuickBooks in its infinite wisdom says:

when this is clearly shown not to be the case (above) and here:

Select TxnID, RefNumber FROM salesreceipt where salesreceipt.TxnID =
(SELECT salesreceipt.TxnID
FROM {oj salesreceipt LEFT OUTER JOIN DepositLine
ON salesreceipt.TxnID = DepositLine.DepositLineTxnID}
Where salesreceipt.DepositToAccountRefFullname = 'Undeposited Funds'
and DepositLine.DepositToAccountRefFullname is NULL)

But I'm guessing that I'm really blowing the capability of QODBC to translate the XML correctly here as the ID isn't the correct one for the TnxID that needs to be deleted. So you will need to get all the TxnIDs into a list or an external table and write a programming loop to delete each sales receipt by its TxnID like this:

DELETE FROM salesreceipt where TxnID = 'F73-1197677537'