Changing format

  • Thread starter Thread starter Sam Thurston
  • Start date Start date
S

Sam Thurston

Hello,

My coding skills are pretty minimal and have been told I
will need to write a module for my following problem.

My problem consists of telephone numbers in an incorrect
format within a table. An example of the current format
within the existing field is 546.432.5432 in need to
change this format to 1-546-432-5432 in a new field within
the same table. I have tried writing several update
queries to no avail. Can anyone help?

Thanks

Sam
 
Sam,

You can do this in an Update Query, as follows:

1. In the first row of your Update Query's grid, drag or select your New
Phone Number field.

2. In the Update to: row, insert the following:

If you are using Access 2002 or newer:

"1-" & Replace([PhoneNum],".","-")

If you are using Access 2000 or older:

"1-" & Left([PhoneNum],3) & "-" &
Mid([PhoneNum],InStr([PhoneNum],".")+1,3) & "-" & Right([PhoneNum],4)


Change PhoneNum to the name of the field which currently holds the phone
number using dots as separators.

hth,
 
Paste the following function in a new code module to make
it public to your application. Then in your update query,
set the new field's value to:

=NewPhoneNumber([currentphonenumberfield])

Function NewPhoneNumber(strOriginal As String) As String
' Convert ###.###.#### to 1-###-###-####

NewPhoneNumber = "1-" & Mid(strOriginal, 1, 3) & _
"-" & Mid(strOriginal, 5, 3) & _
"-" & Mid(strOriginal, 9)

End Function

Kevin Sprinkel
 
Back
Top