MS Access Update Area Code

Question

I am trying to update the area code in my database from 206 to 209 but I have no idea to update that and keep the rest of the phone number. This is what I have right now...

UPDATE Employees
SET HomePhone = 209
WHERE HomePhone Like '*206*';

It is suppose to look like this (209) 555-5555

Answer 1

In this case I recommend that you make the like condition stricter, like this. Otherwise you can update the phones where '206' appears elsewhere (not only in the area code)

UPDATE Employees
SET HomePhone = Replace(HomePhone, "(206) ", "(209) ")
WHERE HomePhone Like '(206) *';