Insert Where <>

[detrie]

Access 2000.

Hi All,

I have 2 tables "Excel and "tImportTemp". I know it's not a great idea to name a table "Excel", but, this is what I am stuck with. Each table has the same fields including a field "OnlineID".

I want to insert everything from tImportTemp into Excel Where OnlineID fields are not =.

Here is my statement:

DoCmd.RunSQL "INSERT INTO Excel SELECT tImportTemp.* FROM tImportTemp WHERE tImportTemp![OnlineID]<>[Excel]![OnlineID]"

When I run this Access complains with Enter Parameter Value Excel!OnlineID.

Any help please?

Thanks
Detrie

 

[KeithG]

try this

DoCmd.RunSQL "INSERT INTO Excel SELECT tImportTemp.* FROM tImportTemp WHERE tImportTemp![OnlineID] Not In(Select Excel.OnlineID from Excel)"

 

[detrie]

Hi KeithG
THANK YOU for your quick response.
That seemed to do it...no more errors but, no records are being moved. I will try to work thru that and post again if I can't work it out.

Thanks again
Detrie

 

[KeithG]

Can you post your db and I can have a look?

 

[detrie]

Hi KeithG..
No records were being moved because I was missing a LEFT JOIN.
This is what worked:

DoCmd.RunSQL "INSERT INTO Excel SELECT tImportTemp.* FROM tImportTemp LEFT JOIN Excel ON tImportTemp.OnlineID = Excel.OnlineID WHERE (((tImportTemp.OnlineID) Not In ([Excel]![OnlineID])))"

Thank you for getting me on the right path.
Detrie