Question

I have a table with a field where data is shown as a text with the following format:

123456789. My table as over 50,000 records.

I would like to run an update query to change the format to: 123-45-6789.

Answer

You will need to use the left, right and mid functions to achieve this.

In the Update to row, enter the following code:

Left([SSN],3) & "-" & Mid([SSN],4,2) & "-" & Right([SSN],4)

NOTE:  Make a copy of the table before you try this, just in case it is not what you want.

Run the query once you have made a copy of the table.

Only run it once as it will give a completely different result the second time around as the data will have changed.

Do you like this tip? Subscribe to my free weekly Newsletter to receive tips weekly via email. Click Here to subscribe.