DELETE Query syntax with Subquery

[BennyLinton]

Any ideas how to DELETE rows in one table that have a field value that matches a field value from another table? Tried these but sytax errors:

DELETE * FROM tmpBankDebitsMinusJE
WHERE tmpBankDebitsMinusJE.ConcatenateBankDebits IN
(SELECT tmpJournalEntryChangeOrders.Concatenate);

DELETE FROM tmpBankDebitsMinusJE
WHERE ConcatenateBankDebits IN
(SELECT Concatenate IN tmpJournalEntryChangeOrders);

 

[CJ_London]

your subquery is incomplete and needs to look more like an 'ordinary' select query

in your last example it should look more like

(SELECT Concatenate [COLOR=red]from[/COLOR] tmpJournalEntryChangeOrders)

 

[CJ_London]

just seen another of your threads and saw this

my table "tmpJournalEntryChangeOrders.Concatenate"

You shouldn't use dots in table names because access will interprete this as a table called tmpJournalEntryChangeOrders with a field called Concatenate.

If you insist on going ths way then you need to surrount your table name with square brackets

e.g. [tmpJournalEntryChangeOrders.Concatenate]