How to reformat phone numbers in a query

[lylyming977]

Hello,



Would someone help me please.

I have this Emp table that I pulled from another source into my database. The phone numbers from this Emp table has different format. Like 703-855-5152 or (703) 823-9322

I want to set the format in a query so it will display all the phone numbers in the same format. field PhoneNum 703-823-9322.

Please tell how I can accomplish this.

Thanks.
Lyly

[ranman256]

If the #s are stored with the acutal characters ( and - then format wont help.
I had to build a function because users put in various forms of phone formats. Put this code in a module :
in the query call: FIXPHONE([phone])

  'put dashes on phone#
Public Function FixPhone(ByVal pvFone)
Dim vOut, v1Chr
Dim i As Integer
Const kFMT = "###-###-####"   'output format
Const kBAD = "-(), "         'illegal chars

pvFone = Trim(pvFone)
If Len(pvFone) > 0 Then
    For i = 1 To Len(pvFone)
      v1Chr = Mid(pvFone, i, 1)
      
      If InStr(kBAD, v1Chr) = 0 Then    'remove unwanted
          vOut = vOut & v1Chr
      End If
    Next
End If
FixPhone = Format(vOut, kFMT)
End Function

[orange]

Here is a function that you could call in your query and a small test routine to show function result.

Public Function FormatPhone(PhoneIn As String) As String

          Dim HoldPhonein As String
10       On Error GoTo FormatPhone_Error

20        HoldPhonein = PhoneIn
30        If Len(Trim(PhoneIn)) > 0 Then
40            If (InStr(PhoneIn, "(")) > 0 Then
50                PhoneIn = Replace(PhoneIn, "(", "")
60                PhoneIn = Replace(PhoneIn, ") ", "-")
70            End If
80        End If
90        FormatPhone = PhoneIn

100      On Error GoTo 0
110      Exit Function

FormatPhone_Error:

120       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FormatPhone of Module AWF_Related"

End Function

Test routine

Sub testFormatPhone()
    Dim a(2) As String, i As Integer
    a(0) = "(204) 456-7489"
    a(1) = "209-345-2345"
    a(2) = "(278) 789-5432"
    For i = 0 To UBound(a)
        Debug.Print "Input to function   " & a(i)
        Debug.Print vbTab & "result of function  " & FormatPhone(a(i))
    Next i
End Sub

OOoops: I see ranman posted while I was testing.

[lylyming977]

Thank you. That's way too advance for me. From the query design view, where do I put this function?

[orange]

What exactly are you trying to do in plain English?
What are you comfortable/experienced with re Queries/vba etc?

The function would reside in a standard module.
Martin Green re modules/functions info

You call the function from your query.

SELECT fld1, fld2, FormatPhone(PhoneNum) from EMP;

Good luck.