Correct phone numbers with incorrect formats

[48Highlander]

When setting up our application we imported some of the data from Excel. Phone numbers entered using the Access application have an input mask that ensures the number is in the format '(999) 999-9999'. The numbers that came from the Excel spreadsheets are in various formats including: '999-999-9999' and '(999)999-9999'

How can I get all of the phone numbers in the table to be in the format that is the same as that required by the input mask?

Bill

 

[PHV]

Something like this ?

Public Function getPhoneNum(strPhone)
Dim i As Integer, x As String, s As String
If Len(Trim(Nz(strPhone, ""))) = 0 Then Exit Function
For i = 1 To Len(strPhone)
  x = Mid(strPhone, i, 1)
  If IsNumeric(x) Then s = s & x
Next i
getPhoneNum = Format(s, "(###) ###-####")
End Function

And then you may execute a simple update query like this:
UPDATE yourTable SET yourPhoneField=getPhoneNum(yourPhoneField);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

A slightly different approach would, perhaps, be to used a slightly modified version of PHV's procedure to strip ALL the non numeric cnars fro ALL the phone #'s and do the formatting just for the UI. Look into setting the 'mask' to not be saved with the data.

MichaelRed
mlred@verizon.net