I am working with a table that has a phone number field. At some point in time the field had an input mask of !999\-000\-000;0;_, so the formatting(dashes) was saved along with the entries. At some point someone changed the mask to !999\-000\-000;;_, so the formatting was not saved along with the entries. I have changed the mask back to !999\-000\-000;0;_, however I am trying to come up with a query to correctly format existing entries that were stored without the dashes.
I tried an update query:
Selected my table
Dragged Phone to Field
In Update To I have: Format("PHONE","!999\-000\-0000;0;_")
In Criteria I have: not like "*-*"
The correct records are being updated, but incorrectly. For example, an entry of 7181234567 is being updated to 999-000-0000 rather than 718-123-4567.
What am I doing wrong?