Knowledge Base

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

Article ID: 210009

Article Last Modified on 6/23/2005

APPLIES TO

Microsoft Access 2000 Standard Edition

This article was previously published under Q210009

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

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

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

MORE INFORMATION

In Microsoft Word, you can create a merge document that uses Microsoft Exchange Server to print to a fax modem. However, if the fax number is not in a specific format, Exchange Server cannot process it. When that happens, Exchange Server presents a Check Names dialog box during the merge for each unrecognized fax number that 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 result of the query 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 then use it as the source of data for the mail merge. However, the Microsoft Access 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 for compatibility with both connection methods.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Start Microsoft Access and open the sample database Northwind.mdb.
Create a module, and then type the following line in the Declarations section, if it is not already present:

Option Explicit

Type or paste the following procedure:

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

Save the module as basFaxNumber, and then close it.
Create a new query in Design view and add the Suppliers table.
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"
On the Query menu, click Make Table.
In the Make Table dialog box, type the name tblMergeFax, and then click OK.
On the Query menu, click Run.
When you see the prompt "Are you sure you want to create a new table with the selected records?", click Yes.
Open the table tblMergeFax.

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]

Use the table tblMergeFax to merge with Microsoft Word.

Keywords: kbhowto kbinfo kbinterop KB210009

Microsoft KB Archive/210009

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

SUMMARY

MORE INFORMATION