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

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

If you use an input mask to format a field that contains phone numbers, but you choose not to store the formatting characters, the phone numbers in this field do not appear formatted when you use the table as the data source of a mail merge in Microsoft Word. In addition, the phone numbers do not appear formatted if you export the table to a text file. This article demonstrates how to use sample Visual Basic for Applications code with either an update query or a select query to create a new field that contains the phone numbers with correct formatting characters.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

MORE INFORMATION

There are two methods for formatting phone numbers so that both the data and formatting characters appear when you perform a mail merge in Microsoft Word or when you export the data to a text file. You can use an update query to change the data in the original table so that it includes the formatting characters. Or you can create a select query that contains an expression that returns the phone number data with the formatting characters. You can then export the select query to text or use it as the source of a mail merge in Microsoft Word. Both of the following examples use a sample Visual Basic for Applications procedure to format the phone number.

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 - 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.

REFERENCES

For more information about input masks, search the Help Index for "Create an input mask to control how data is entered in a field or control," or ask the Microsoft Access 97 Office Assistant.