How do you automagically insert dashes in different phone number lengths?

[anoble1]

I am trying to make a phone number lookup.
Is there a way to automatically put a dash in a number. We have 2 types of numbers.

1. x-xxx-xxxx
2. xxx-xxx-xxxx

Not sure if there is a way to do both of them, but would be cool.

[Seth Schrock]

To do this, it has to be a text field. You could then use the Len() function to get the length of the data. Then you can use a select case statement to see if there are 8 characters (the first example), 10 characters (the second example) or "else" (invalid entry). You would then need to split up your string into three parts and then concatenate them back together with dashes in between. Here is some air code:

Dim s1 as string
Dim s2 as string
Dim s3 as string
 
Select Case Len(Me.MyField)
    Case 8
        s1 = Left(Me.MyField, 1)
        s2 = Mid(Me.MyField, 2, 3)
        s3 = Mid(Me.MyField, 5)
 
    Case 10
        s1 = Left(Me.MyField, 3)
        s2 = Mid(Me.MyField, 4, 3)
        s3 = Mid(Me.MyField, 7)
 
    Case Else
        MsgBox "Invalid Entry"
        Exit Sub
 
End Select
 
Me.MyField = s1 & "-" & s2 & "-" & s3

The actual numbers of which characters are being grabbed might be off as I seldom get it right the first time, but the idea will work.

[ADezii]

Assuming the Phone Number Field is TEXT in nature and consists of either 8 or 9 characters, it can easily be Formatted as you requested:

1. SQL:
   

   SELECT tblEmployees.[Phone#], Format$([Phone#],"&&@-@@@-@@@") AS FPhone
   FROM tblEmployees;

2. Results
   

   Phone#       FPhone
   12345678     12-345-678
   123456789    123-456-789
   456789875    456-789-875
   998076412    998-076-412
   12346677     12-346-677
   222113145    222-113-145
   878878787    878-878-787
   99999999     99-999-999
   23326526     23-326-526