How to update rows in one table depending on another table 1

[JohnMusbach]

This is a follow up to the closed thread thread700-1241490.

Hi, I have one table (Geocodes) who has identical fields as another table (PROJDES1) except that its fields have updated contents and I would like to merge the fields contents into the other table (PROJDES1) without touching the other fields in that table. I am attempting to do this with the following code:

DoCmd.RunSQL "UPDATE Geocodes INNER JOIN PROJDES1 " & _
       "ON 'Geocodes.Project #' = 'PROJDES1.Project #' " & _
"Set Geocodes.LAT = PROJDES1.LAT " & _
"WHERE Geocodes.LAT <> PROJDES1.LAT " & _
   "OR PROJDES1.LAT IS NULL " & _
   "OR Geocodes.LAT IS NULL"

But when I run the function access says "Join expression not supported.", what am I doing wrong? Thanks!

 

[lespaul]

looks like you've got some extra ' marks and missing the correct delimiter for names with spaces:

DoCmd.RunSQL "UPDATE Geocodes INNER JOIN PROJDES1 " & _
       "ON Geocodes.[Project #] = PROJDES1.[Project #] " & _
"Set Geocodes.LAT = PROJDES1.LAT " & _
"WHERE Geocodes.LAT <> PROJDES1.LAT " & _
   "OR PROJDES1.LAT IS NULL " & _
   "OR Geocodes.LAT IS NULL"

now you know the only field that is going to change is PROJDES1.LAT, right?

Leslie

Have you met Hardy Heron?

 

[JohnMusbach]

Yep I'm aware of that and thanks, your corrections have fixed the code.