Delete query won’t delete 1

[barrysprout]

I am trying to delete records from one table based on the results of a select query.

I have a table called ‘Invoices’ where I want to delete those invoices that are returned by a select query called ‘DeleteInv’. In the delete query design, I have linked this query to the Invoices table by the field ‘InvoiceNo’. When I run it, I get a ‘can’t delete’ message. The cascade delete option allows the record to be deleted and I can delete it if I do it manually.

What am I doing wrong?

 

[PHV]

Could you please post the SQL code of your (non working) DELETE query and of DeleteInv ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barrysprout]

The SQL of both queries is (using the SQL button on the QBE grid) is:

Select query:

SELECT Shipments.ShipID, JCLRInv.InvoiceNo
FROM Shipments LEFT JOIN (CLRs LEFT JOIN JCLRInv ON CLRs.CLR = JCLRInv.CLR) ON Shipments.ShipID = CLRs.ShipID
WHERE (((Shipments.ShipID)=[Forms]![Shipbook]![ShipID]));

Delete query:

DELETE Invoices.*
FROM DelInv INNER JOIN Invoices ON DelInv.InvoiceNo = Invoices.InvoiceNo;

I have a feeling it won't work because the final delete query isn't updatable (due to the DelInv query) in which case I would have to pass the InvoiceNo criterion to a simple delete query consisting of only the Invoices table. Maybe there's an easier way?

 

[PHV]

You may try this:
DELETE FROM Invoices
WHERE InvoiceNo In (SELECT InvoiceNo FROM DelInv WHERE InvoiceNo Is Not Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886