Delete query cannot find required entry

[AbbasBD]

I want to delete entry in table [tBuy Invoice] where linked table [tBuys] has no record. Statement is -
-----------
DELETE [tBuy Invoice].*
FROM [tBuy Invoice]
WHERE ((([tBuy Invoice].BuyInvcID)
In (
SELECT [tBuys].[BuyInvcID]
FROM [tBuys]
WHERE [tBuys].[BuyID] Is Null
)));
-------------
But the Delete query do not find any entry. I think it is because 'Is Null' do not return 'no entry list', but what can be done?

Thanks

[twinnyfo]

AbbasBD,

I would use a LEFT JOIN Query, which lists all records from [tBuy Invoice], whether there is amatching record in [tBuys] or not. Then, set the criteria for [tBuys].[BuyInvcID] Is Null, to only return the records that don't have a corresponding record in [tBuys]

DELETE [tBuy Invoice].*, tBuys.BuyInvcID
FROM [tBuy Invoice] 
LEFT JOIN tBuys 
ON [tBuy Invoice].BuyInvcID = tBuys.BuyInvcID
WHERE tBuys.BuyInvcID Is Null;
 

Hope this helps!

[Rabbit]

I have no idea what this means:

'Is Null' do not return 'no entry list'

What is a 'no entry list'? You need to define the terms that you're using.

[AbbasBD]

twinnyfo

exact solution!

thanks

[AbbasBD]

Rabbit

For 'no entry list' I meant tBuys has no record corresponding to specific entry in [tBuy Invoice].

Thanks for your reply.