Delete query (Access 2000)

[freelance]

In my delete query i get the error " specify the table containing the records you want to delete.Can you help ?


DELETE orders.orderid, orders.Audit, customers1.Customerid
FROM orders INNER JOIN customers1 ON orders.customerid = customers1.Customerid
WHERE (((orders.orderid)<DMax([orderid],[orders],[Audit]=True)) AND ((orders.Audit)=True) AND ((customers1.Customerid) In (118,119,120,121,124,960,1008,402)));

[HansV]

It's always difficult to create a delete query based on more than one table. Why do you need the Customers1 table in the query? You could put the condition on the CustomerID field from Orders instead of on the CustomerID field from Customers1.

[freelance]

Thank you for the reply.I get now the message enter parameter value.I am sending you the query, maybe you could find some other way ? In this case i want to delete all the orders having Audit = true and Customerid = 124

[HansV]

All arguments to DMax are strings. The SQL should be

DELETE orders.orderid, orders.Audit, orders.customerid
FROM orders
WHERE (((orders.orderid)<DMax("[orderid]","[orders]","[Audit]=True")) AND ((orders.Audit)=True) AND ((orders.customerid)=124));

Since you have carefully supplied a table in which none of the records satisfy these conditions, no records will be deleted.

[freelance]

It is great ! I get the results required and the code runs and flies. Thank you very much !!