How to delete records in one table based on list in second table 1

[Maturi]

Hi,

I have 2 tables

tbl_Docs with Fields:
- ID1 (Autonumber)
- DocRef (Integer)
- Others (Text)

tbl_DeleteList with fields
- ID2 (Autonumber)
- DocToDelete (Integer)

I want to write a query to delete those records in tbl_Docs where the DocRef matches the list in tbl_DeleteList (field DocToDelete)

I know how to do the simple delete for a single record

DELETE tbl_docs.* FROM tbl_docs WHERE (((tbl_docs.DocRef)=2))

but I can't get it to work by reading the list in tbl_DeleteList. I've tried.....

DELETE tbl_Docs.*, tbl_Docs.DocRef FROM tbl_Docs INNER JOIN tbl_DeleteList ON tbl_Docs.DocRef = tbl_DeleteList.DocToDelete
WHERE (((tbl_Docs.DocRef)=[tbl_DeleteList]![DocToDelete]))

And

DELETE tbl_Docs.*, tbl_Docs.DocRef  FROM tbl_Docs, tbl_DeleteList WHERE (((tbl_Docs.DocRef)=[tbl_DeleteList]![DocToDelete]))

I've derived the code from the Query Builder. I get error message
"Could not delete from specified tables"


Neither work - Can you help?

Thanks

 

[PWise]

try

DELETE tbl_docs.* FROM tbl_docs WHERE tbl_docs.DocRef in (select DocToDelete from DeleteList )

 

[PHV]

Have you tried this ?
DELETE tbl_Docs.* FROM tbl_Docs INNER JOIN tbl_DeleteList ON tbl_Docs.DocRef = tbl_DeleteList.DocToDelete

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

 

[Maturi]

Pwise

Thanks - this worked great. I didn't even know about the IN statement.

Thanks once again

M