Type mismatch probs when deleting multiple records (1 Viewer)

[Howlsta]

Hi Readers,

I really hope someone can help with this. I want to look through 2 tables and if the PointID in one table does not appear in another table in a field called Accident Key then I want to delete the record. The code I have used is below, similar code works on other tables, but PointID is a text field and Accident Key is autonumber. How should I modify this code to avoid a type mismatch.

sSQL = "DELETE FROM [PNT_Accs] WHERE [PNT_Accs]![PointID] NOT IN (SELECT [Accident Key] FROM [Attendant circumstances])"
g_DataConnection.Execute sSQL

would I have to use CInt on the PointID? Would this mean I would have to do it differently to using SQL.

Any thoughts appreciated as always,

Rich

oh and BTW way PointID has to be text rather than number, because it is needed by MapX as text, don't know why but there it is.

 

[Howlsta]

It was quite simple in the end, just a case of getting some syntax correct:

sSQL = "DELETE FROM [PNT_Accs] WHERE [PNT_Accs]![PointID] NOT IN (SELECT clng([Accident Key]) FROM [Attendant circumstances])"
g_DataConnection.Execute sSQL