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])<>”USA”, 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 vbCrLfto indicate a CR + LF (car-
riage 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.TIPTIP
Part II Writing VBA Code to Exchange Data between Office Components