Need advice on my Update Query

Question

I'm trying create an update query which supposed to change the field value (Default value is “Delhivery") to "IndiaPost" when the corresponding Pin code is not found in the table "Pin code Whitelist".

It always gives me an “Unknown” error while running the query, even though no syntax errors were displayed. I hope these attached pictures will give you enough clarity on this.

Screenshot-Main Database fields

Screenshot-Query Window with error

Screenshot-Table " Pincode Whitelist"

Correction- On the "Screenshot-Query Window with error]", criteria is as given bellow:

 DCount("*", "[Delhivery Pincode List]", "[pin]=[MainDb]![Pincode]")

Sorry for the mistake.

Expecting your valuable advice to identifying the mistake and fixing it.

Thank you very much in anticipation.

Answer 1

I would recommend a Not In statement in your update query:

UPDATE MainDb SET [shipping choice] = "IndiaPost"
WHERE (((pincode) Not In (select pincode from [Delhivery Pincode List])));