ACC: How to Format Fax Numbers to Merge with Microsoft Word

ID: Q155134


The information in this article applies to:
  • Microsoft Access versions 7.0, 97


SUMMARY

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

This article shows you how to format a fax number in Microsoft Access so that you can merge it with a Microsoft Word document that uses Microsoft Exchange to print to a fax modem.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.


MORE INFORMATION

In Microsoft Word, you can create a merge document that uses Microsoft Exchange to print to a fax modem. However, if the fax number is not in a specific format, Microsoft Exchange cannot process it. When that happens, Microsoft Exchange presents a Check Names dialog box during the merge for each unrecognized fax number it encounters.

The fax number data must be in the following format

[FAX:<number>]
where <number> is the fax number you are dialing. For example:
[FAX:12065551212]
The following example uses the sample database Northwind.mdb. It shows you how to create a procedure to format your fax numbers, and then uses the procedure in a make-table query. The resulting new table contains only records that have 7- or 10-digit fax numbers.

NOTE: During a mail merge, Microsoft Word can use dynamic data exchange (DDE) or Open Database Connectivity (ODBC) to connect with Microsoft Access. DDE, the most common connection type, recognizes a user-defined procedure in a query. If you use DDE, you can make the query in this example a select query, and use it as the source of data for the mail merge. However, the Microsoft Access 7.0 and 97 ODBC drivers produce an error when you use a select query because the drivers do not recognize the user-defined procedure. The following example uses a make-table query instead of a select query in order to be compatible with both connection methods.

  1. Open the sample database Northwind.mdb.


  2. Create a module and type the following line in the Declarations section:
    
           Option Explicit 


  3. Type the following procedure:

    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.
    
           Function FaxNumFormat(FaxNum)
    
           ' If the fax number field is empty, return "Invalid Fax" and exit.
           If IsNull(FaxNum) Then
              FaxNumFormat = "Invalid Fax Number"
              Exit Function
           End If
    
           Dim Counter As Integer
           Dim NewString As String
    
           ' Strip out all characters except numbers.
           For Counter = 1 To Len(FaxNum)
              If IsNumeric(Mid(FaxNum, Counter, 1)) Then
                 NewString = NewString & Mid(FaxNum, Counter, 1)
              End If
           Next Counter
    
           ' If NewString is 10 digits long and the call is local, format the
           ' digits to the right of the area code.
           If Len(NewString) = 10 And _
              Left(NewString,3) = "<YourAreaCode>" Then
              NewString = Right(NewString,7)
              NewString = Format(NewString,"\[\F\A\X\:#\]")
    
           ' If NewString is 10 digits long, and the call is long distance, add
           ' the long distance prefix '1' to the beginning, then format
           ' accordingly.
           ElseIf Len(NewString) = 10 Then
              NewString = "1" & NewString
              NewString = Format(NewString, "\[\F\A\X\:#\]")
           ' If NewString is 7 digits long, format accordingly.
           ElseIf Len(NewString) = 7 Then
              NewString = Format(NewString, "\[\F\A\X\:#\]")
           Else
              ' All other numbers are invalid.
              NewString = "Invalid Fax Number"
           End If
           FaxNumFormat = NewString
    
          End Function 


  4. Save the module as basFaxNumber and close it.


  5. Create a new query in Design view and add the Suppliers table.


  6. In the query grid, add the following fields:
    
           Field: CompanyName
              Show: Yes
           Field: ContactName
              Show: Yes
           Field: FaxNbr: FaxNumFormat([Fax])
              Show: Yes
              Criteria: <>"Invalid Fax Number" 


  7. On the Query menu, click Make Table.


  8. In the Make Table dialog box, type the name tblMergeFax and click OK.


  9. On the Query menu, click Run.


  10. When you see the prompt "Are you sure you want to create a new table with the selected records?" click Yes.


  11. Open the table tblMergeFax.


  12. Note that all records in the table contain the correct fax number format in the FaxNbr field. For example:
    
           CompanyName                 ContactName     FaxNbr
           -------------------------   -------------   -----------------
           Grandma Kelly's Homestead   Regina Murphy   [FAX:13135553349] 


  13. Use the table tblMergeFax to merge with Microsoft Word.



REFERENCES

For more information about faxing documents in Microsoft Word, please see the following articles in the Microsoft Knowledge Base:

Q137160 How to Fax a Document Using File Send and Exchange
Q142344 Merging to Fax Unexpectedly Causes Exchange to Check Names
For more information about merging data with Microsoft Word, search the Help Index for Merging data with Microsoft Word Mail Merge Wizard or ask the Microsoft Access 97 Office Assistant.

Additional query words:

Keywords : kbinterop ExrStrg
Version : WINDOWS:7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: October 14, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.