Update field based on mismatched Valies

Question

I am trying to check the value from one table against the value in another table, and then update a field in the first table when they dont match but I cant get the code to work.

What I've tried so far;

DoCmd.RunSQL "UPDATE Issues SET Message='There is an issue at or before the sex code Field' WHERE Issues.[Sex Code] = Ref.[Sex Code] AND Ref.[Sex Code] Is Null));"

DoCmd.RunSQL "UPDATE Issues SET Message = 'There is an issue at or before the Sex Code field' LEFT JOIN Ref ON Issues.[Sex Code] = Ref.[Sex Code] WHERE ((Ref.[Sex Code]) Is Null);"

DoCmd.RunSQL "SELECT Issues.[Sex Code] FROM Issues LEFT JOIN Ref ON Issues.[Sex Code] = Ref.[Sex Code] WHERE Ref.[Sex Code] Is Null UPDATE Issues SET Message='There is an issue at or before the Sex Code Field'"

Answer 1

Using your above idea, what you need is something like the following, HOWEVER are you sure you only want to join on Sex Code and no other field (like name, ID #, etc.)?

UPDATE Issues LEFT JOIN Ref ON Issues.[Sex Code] = Ref.[Sex Code] SET Issues.Message = 'There is an issue at or before the Sex Code field'
WHERE (((Ref.[Sex Code]) Is Null));

To allow for multiple errors, you can just append to the 'message' field like the following (keeps any prior messages):

UPDATE Issues LEFT JOIN Ref ON Issues.[Sex Code] = Ref.[Sex Code] 
SET Issues.Message = [Issues]![Message] & 'The Sex Code field is Null' & '; '
WHERE (((Ref.[Sex Code]) Is Null));