Deleting Access records by query when join is involved

[KevinsHandle]

I am having a brain freeze the Morning. I've done this type of query many times but it's not coming to me today.

I believe that Access is barking at the join in the qry.

I want to delete records from tblClaimsData when the tblClaimsData.ServiceDate is not Between the tblVendorStatus.EffDate and tblVendorStatus.TermDate or tblClaimsData.VendorID Is Null.

I have a vague recollection of using a sub query to select the joined records that are to be deleted with an "In Select yad a yada"

Any help appreciated.

Kevin

 

[genomon]

I believe that Access is barking at the join in the qry.

Please post back the SQL of the query...

Beir bua agus beannacht!

 

[KevinsHandle]

Here it is:

DELETE DISTINCTROW tblClaimsData.* FROM tblClaimsData LEFT Join tblVendorStatus ON tblClaimsData.Vendor_ID = tblVendorStatus.VendorNo
WHERE (((tblClaimsData.Service_Date Not BETWEEN tblVendorStatus.EffectiveDate AND tblVendorStatus.TermDate)) OR(((tblClaimsData.Vendor_ID) IS NULL();

The goal here is to remove claims when the date of service for the claimed procedure falls outside the contract term of the vendor.

Thx for any help

Kevin

 

[PHV]

What about this ?
DELETE tblClaimsData.*
FROM tblClaimsData INNER Join tblVendorStatus ON tblClaimsData.Vendor_ID = tblVendorStatus.VendorNo
WHERE (tblClaimsData.Service_Date Not BETWEEN tblVendorStatus.EffectiveDate AND tblVendorStatus.TermDate)
OR tblClaimsData.Vendor_ID IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KevinsHandle]

Thanks for answering but that gets the same error. I am pretty sure that the structure required in the query is of the nature of :
DELETE tblName.* FROM tblName WHERE FieldX IN(SELECT ..... (with joins etc in here)

My problem is I forget the syntax.

Thx

Kevin

 

[PHV]

that gets the same error
Which error ?
Anyway, what about this ?

DELETE *  FROM tblClaimsData
WHERE Vendor_ID IS NULL OR Vendor_ID IN (
SELECT VendorNo FROM tblVendorStatus WHERE tblClaimsData.Vendor_ID = tblVendorStatus.VendorNo
AND NOT (tblClaimsData.Service_Date BETWEEN tblVendorStatus.EffectiveDate AND tblVendorStatus.TermDate)
)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[KevinsHandle]

Error is "Could not delete from specified table.


I your version is generally it.

Thx much.

The fog is lifting.

Kevin