An Update SQL 1

[fbacchus]

Hi All:

I haven't used Access in a while, so bear with me. I am working with 2 files (1) TableA (an Access 2000 format database) and (2) FileB (an Excel file). I am trying to write an update SQL that would update TableA-Field-1 with FileB-Field-1 only if TableA-Field-1 is blank or is Nulls and FileB-Field-1 is NOT blank. If this condition is met then the UPDATE is executed and the process continues to updating TableA-Field-2 with FileB-Field-2, then TableA-Field-3 with FileB-Field-3 and so on...

As you can see each condition is only applicable to the field it is going to update. Here is what I am coming up with but I don't think it is correct:

UPDATE TableA LEFT JOIN [FileB] ON TableA.[Full Name] = [FileB].Contact_Name SET TableA.Main_Email = [FileB].Email_Address, TableA.CC_Email = [FileB].CC_Email_Address
WHERE (((TableA.Main_Email) Is Null Or (TableA.Main_Email)=" ") AND ((TableA.CC_Email) Is Null Or (TableA.CC_Email)=" ") AND (([FileB.Email_Address])<>" " Or ([FileB.Email_Address]) Is Not Null) AND (([FileB].CC_Email_Address)<>" " Or ([FileB].CC_Email_Address) Is Not Null));

I would appreciate your suggestions. Thanks in advance.


fb

 

[MajP]

I believe you need something like this

UPDATE 
 tblOne 
INNER JOIN 
 tblTwo 
ON 
 tblOne.ID = tblTwo.ID 
SET 
 tblTwo.field1 = IIf(IsNull([tblTwo]![field1]),[tblOne]![field1],[tblTwo]![field1]), 
 tblTwo.field2 = IIf(IsNull([tblTwo]![field2]),[tblOne]![field2],[tblTwo]![field2]), 
 tblTwo.field3 = IIf(IsNull([tblTwo]![field3]),[tblOne]![field3],[tblTwo]![field3]);

 

[fbacchus]

Thanks MajP. I am assuming that in your example, tblOne is my TableA and tblTwo is my FileB. Correct?

thanks


fb