You can do an update from another table can't you? 1

[nuVBer]

The first record in Amerstan_NewMonth always ends up with no match even thou
there is one...
Each record in the left table has a matching "GC_Claim" record in the right
table.

Can you see why this doesn't work (You can do an update from another table
using a join, can't you??????)



strSQL = "UPDATE Amerstan_NewMonth LEFT JOIN Clmno_Conversion " & _
"ON Amerstan_NewMonth.gc_claim = Clmno_Conversion.GC_CLAIM " & _
"SET Amerstan_NewMonth.claim_numb = Clmno_Conversion.CONV_CLM"
db.Execute strSQL

 

[tlbroadbent]

You should use an INNER JOIN rather than a LEFT JOIN.

strSQL = "UPDATE Amerstan_NewMonth INNER JOIN Clmno_Conversion " & _
"ON Amerstan_NewMonth.gc_claim = Clmno_Conversion.GC_CLAIM " & _
"SET Amerstan_NewMonth.claim_numb = Clmno_Conversion.CONV_CLM"
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.