Delete Subquery Error

[Trudye]

My Delete Subquery is not working. It is returning all of the records in the table. When I run the subquery (SELECT statement) by itself I get the correct subset of records.

However when I add the DELETE statement it returns the entire table. When I try to you anything other than EXISTS I get an error.

DELETE * FROM tblCompiledExceptions_RPM1 WHERE Exists
(SELECT tblCompiledExceptions_RPM1.*, SMM_VEH_EVENTS_ENH_T.LEASE_ACCT_NUM, SMM_VEH_EVENTS_ENH_T.LEASE_ACCT_NUM, SMM_VEH_EVENTS_ENH_T.VEH_RECVRY_DATE
FROM tblCompiledExceptions_RPM1 INNER JOIN SMM_VEH_EVENTS_ENH_T ON tblCompiledExceptions_RPM1.fldVin = SMM_VEH_EVENTS_ENH_T.VIN_NUM
WHERE (((SMM_VEH_EVENTS_ENH_T.LEASE_ACCT_NUM)=Right([tblCompiledExceptions_RPM1].[LeaseNumber],9)) AND ((SMM_VEH_EVENTS_ENH_T.VEH_RECVRY_DATE)<Date())));

Any ideas?
Thanx
Trudye

 

[PHV]

Perhaps this ?
DELETE FROM tblCompiledExceptions_RPM1
WHERE Exists(SELECT * FROM SMM_VEH_EVENTS_ENH_T
WHERE VIN_NUM=tblCompiledExceptions_RPM1.fldVin
AND LEASE_ACCT_NUM=Right([tblCompiledExceptions_RPM1].[LeaseNumber],9)
AND VEH_RECVRY_DATE<Date());

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