How do you format telephone numbers in Access?

Question

I have the tel# [phone] for example.
And I need it to look like [phone].
I have a list of these I need to format in Access.

Answer 1

I agree with lucee above. But, here is a quick and dirty function to get what you desire :

Function FormatPhoneNumber(ByVal text As String) As String
' Modify a phone-number to the format "XXX-XXXX" or "(XXX)XXX-XXXX".

Dim i As Long

' ignore empty strings
If Len(text) = 0 Then Exit Function

' get rid of dashes and invalid chars
For i = Len(text) To 1 Step -1
If InStr("0123456789", Mid$(text, i, 1)) = 0 Then
text = Left$(text, i - 1) & Mid$(text, i + 1)
End If
Next
' then, re-insert them in the correct position
If Len(text) <= 7 Then
FormatPhoneNumber = Format$(text, "!@@@-@@@@")
Else
FormatPhoneNumber = Format$(text, "!(@@@)@@@-@@@@")
End If
End Function

Just drop that in a public module, and insert the function() into your update query.

Good luck!

Answer 2

You have a separate field for area code, prefix (465) and the last four numbers. That way, you can sort by area code or prefix.

Answer 3

Set up an Input Mask.