ACC: How to Format Phone Number Fields Using an Update Query

MORE INFORMATION

Update Query

1. In Microsoft Access, create a new database named PhoneDB.
2. Create the following new table named tblPhoneNumbers:

          tblPhoneNumbers
          --------------------------
          Field Name: OriginalNumber
             Data Type: Text
          Field Name: NewNumber
             Data Type: Text
   					

3. Click the row selector to the left of the Original Number field. Start the Input Mask Wizard by clicking the Build button (...) to the right of the Input Mask property.
4. In the first screen of the Input Mask Wizard, select Phone Number from the Input Mask list and click Next twice.
5. When asked how you want to store the data, select the option to store the data without the symbols in the mask, and then click Finish.
6. Save and close the table.
7. Open the tblPhoneNumbers table in Datasheet view and add the following two records:

          OriginalNumber         NewNumber
          --------------         ---------
          (111) 123-4567
          (   ) 345-6789
   						

   NOTE: No values are entered in the NewNumber field.
8. Close the table, click the Modules tab, and then click New.
9. Type or paste the following code into the new module:
   
   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Microsoft Access 2.0.

          Function ConvertPhone(phone As String)
             Dim PhoneLen As Integer
   
             On error Goto ConvertPhone_err
   
             ' Determine if a valid number exists. If not, exit function.
   
             If Len(phone) = 7 Or Len(phone) = 10 Then
                PhoneLen = Len(phone)
             Else
                Exit Function
             End If
   
             ' Format number and return value to function name.
   
             Select Case PhoneLen
                Case 7
                   ConvertPhone = "(   ) " & Left(phone, 3) & _
                      "-" & Right(phone, 4)
                Case 10
                   ConvertPhone = "(" & Left(phone, 3) & ") " _
                      & Mid(phone, 4, 3) & _
                      "-" & Right(phone, 4)
             End Select
   
          ConvertPhone_exit:
             Exit Function
   
          ConvertPhone_err:
             ' Use the following line if you are
             ' running Microsoft Access 7.0 or 97.
             MsgBox Cstr(Err) & " " & Err.Description
             ' Use the following line if you are
             ' running Microsoft Access 2.0.
             ' MsgBox CStr(Error) & " " & Error(Err)
             Resume ConvertPhone_exit
   
          End Function
   					

10. If you are using Microsoft Access 97, click Compile And Save All Modules on the Debug menu. If you are using Microsoft Access 7.0, click Compile All Modules on the Run menu; then click Save All Modules on the File menu. If you are using Microsoft Access 2.0, click Compile Loaded Modules on the Run menu; then click Save on the File menu. Close the Module.
11. In the Database Window, click Queries. Click New.
12. If you are creating a new query in Microsoft Access 2.0, click New Query; otherwise, click Design View.
13. In the Show Table dialog box (or Add Table dialog box in Microsoft Access 2.0), select tblPhoneNumbers, and then click Add. Close the Show Table or Add Table dialog box.
14. Add the NewNumber field to the Query By Example grid; on the Query menu, click Update Query (or Update if you are using Microsoft Access version 2.0).
15. Type the following expression on the Update To line under NewNumber:

       ConvertPhone([OriginalNumber])
    					

16. On the File menu, click Save and save the query as qryPhone.
17. On the Query menu, click Run.
18. After the query is finished running, open the table tblPhoneNumbers and note that the NewNumber field has been updated with new data. If you use this table as a data source for a mail merge in Microsoft Word, or if you export this table to text, note that the data in the NewNumber field contains the formatting symbols.

Select Query

1. Follow steps 1 through 13 in the "Update Query" section. It is not necessary, however, to create the field NewNumber in step 2.
2. Type the following expression in the first Field box in the query grid:

      NewPhone: ConvertPhone([OriginalNumber])
   					

3. Save the query as qrySelectPhone. Note that if you export qrySelectPhone to text or use it as the data source of a mail merge in Microsoft Word, the data in the field NewPhone includes the formatting symbols.