Using a Query to Concatenate Data for Export

I like to create a select query to use as the data source for merging Access data to Word, concatenating data from various fields as needed for best results when working with Word documents. One field concatenates name, job title, and company name information, using the IIf function to avoid creating blank lines, and another creates a single field with address information. This technique ensures that you won't see blank lines in the address block in a letter, or on a label, even if some fields lack data. I also create a separate ZipCode field for use in creating U.S. PostNet bar codes on envelopes or labels.

In the sample database, this query is qryContactsForMerge. The calculated field expressions I used to concatenate data from the simple flat-file tblContacts are listed next. Depending on the fields in your table(s), these expressions will need to be customized — for example, to deal with multi-field addresses or name prefixes and suffixes:

ContactName:

[FirstName] & " " & [LastName] NameTitleCompany:

[FirstName] & " " & [LastName] & Chr(13) & Chr(10) & [JobTitleCompany]

JobTitleCompany: IIf(Nz([JobTitle])="" And

Nz([CompanyName])="",""<IIf(Nz([JobTitle])<>""<[JobTitle] & IIf(Nz([CompanyName])<>"",Chr(13) & Chr(10) & [CompanyName]),[CompanyName]))

CityStateZip:

[City] & " & [StateOrProvince] & " " & [PostalCode] WholeAddress:

[StreetAddress] & Chr(13) & Chr(10) & [CityStateZip] & IIf(Nz([Country])<>"" And Nz([Country])<>MUSAM, Chr(13) & Chr(10) & [Country],"")

ZipCode:

IIf([Country]="USA" Or Nz([Country])="",[PostalCode]<"") LastNameFirst

[LastName] & IIf([FirstName],", " & [FirstName],"")

In VBA code, you can use the VB named constant vbCrLf to indicate a CR + LF (carriage return plus linefeed) to start a new line in a text string, but named constants can't be used in query field expressions, so I use the Chr(13) & Chr(10) syntax instead, using the numeric values of the CR and LF characters.

Using a query to do the concatenating (rather than creating expressions in VBA code) makes it much easier to verify that the expressions are returning the correct data, and to fix any problems before doing the merge. After creating the expressions, just switch to datasheet view to inspect the results, and then switch back to design view to fix any problems you see.

Choosing a Method for Merging Access Data to Word

The NewDocFromTemplate procedure listed in the previous section lists Word document properties that can be filled with Access data. This is my preferred method for exporting Access data to Word documents, but it is not the only method. You can also export Access data to Word bookmarks, or simply insert data into a Word document using the TypeText method. And then there is mail merge, which is most suitable for merging data from very large numbers of records. Table 6.1 compares the advantages and disadvantages of these methods.

TABLE 6.1

Comparison of Four Ways to Merge Access Data to Word

Method

Advantages

Disadvantages

Bookmarks

There is no need to open the properties sheet; bookmarks are inserted directly into the template.

Bookmarks are more familiar to Word users than document properties.

Creates a separate document for each record, which allows easy customization of specific documents.

There is no link to the Access database, so documents can be opened even on another computer.

You can't insert the same bookmark twice in a template; to display the same information in two or more places, you either need to create another bookmark or use a cross-reference field that references the first bookmark.

Users may inadvertently type into the text inside a bookmark, overwriting the exported value.

Document Properties

Data from a document property can be displayed in multiple locations, using fields.

Creates a separate document for each record, which allows easy customization of specific records.

There is no link to the Access database, so documents can be opened even on another computer.

Requires creating document properties in the template, in the custom tab of the properties sheet.

continued

I TABLE 6.1

| (continued)

Method

Advantages

Disadvantages

TypeText

No advance preparation of any kind is needed; this method works with a document created from the default Word template, or a default labels document.

There is no link to the Access database, so documents can be opened even on another computer.

Suitable only for very simple documents, such as mailing labels or tabular lists.

Mail Merge

Suitable for merging very large numbers of records, too large to create an individual document for each record.

Customization of individual records is difficult, because all data is merged to a single document.

Creating a mail merge labels document is more complex than creating a labels document for use with the TypeText method.

You can work with Word 97/2003 documents in Word 2007, as well as create new documents in the new Word 2007 format, so you don't need to redo all your templates just to get them to work in Office 2007. Some of the templates used for Word merge in the sample Word Export database are in Word 2007 format, and others are in Word 97/2003 format. The extensions differ for these two formats; new documents have the .docx extension, and new templates have the .dotx extension, whereas older ones have the .doc or .dot extensions, as shown in Figure 6.8. When you open a document or template in the older format, the title bar says "(Compatibility Mode)" after the file name.

The new Type column in the Windows Vista Explorer shows the contents of a Word 2007 document's Keywords field, so you can use this built-in Word property to display relevant information in the Explorer.

0 -1

Post a comment