Problems Mail Merging Duane Hookom's Concatenate Function To Word.

[mfosterla]

Hello Everyone,

I have been working on a project where I need to perform a Mail-Merge to word with several records. I have a sub-report which I need to Concatenate to one Merge-Field. I am having problems getting Duane Hookom's Generic Function To Concatenate Child Records to work.

The function I am trying to use is located at the following address:

You must copy and paste the URL in a browser....

--------------------------------------------------------------------------------------

http://www.rogersaccesslibrary.com/...eneric Function To Concatenate Child Records'

--------------------------------------------------------------------------------------

I was able to get the Concatenate sql query to work perfectly in MS Access but when I tried to perform the Mail-Merge to word I am getting the following error:

Word was unable to open the data source.

I have right clicked the query in MS Access and tried to export it to a mail merged word document. I selected a new document and I get a select table dialog box with only the tables listed and none of the concatenated queries. I clicked options and selected the views check box and was able to see the concatenated query. When I select it I get the following error message box in word:

Word was unable to open the data source.

I have tried the mail merge wizard in word and I am not even able to see the concatenated query as an option to select.

I created a standard union query in ms access just to test it and I was able to merge it to a word document with no problems.

I have tried using ADO and DAO in the basConcatenate module and still no luck. Is there something I am needing to merge the concatenated queries like a reference in VBA for Word or Access.

Any help I can get would be very much appreciated....

Thanks,

Mark Foster

 

[PHV]

You may create a temporary table based on your concatenate query and then use this table as the mailmerge data source.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH is correct. I almost always push records to a mailmerge file and then use it as the data source.

BTW: the code can also be found faq701-4233

Duane
Hook'D on Access
MS Access MVP

 

[mfosterla]

PHV,

Thanks so much for your quick response. I created a temporary merge table and field for the mail merge and it worked. I used the following SQL query to insert the concatenated merge field:

---------------------------------------------------------------------
INSERT INTO LessorsMerged ( LeaseNo, Lessors )
SELECT Lease.LeaseNo, Concatenate("SELECT Lessors.CompanyName & ' ' & Lessors.PrimaryFirstName & ' ' & Lessors.PrimaryMiddleName & ' ' & Lessors.PrimaryLastName & ' ' & Lessors.FirstBinding & ' ' & Lessors.SecondaryFirstName & ' ' & Lessors.SecondaryMiddleName & ' ' & Lessors.SecondaryLastName & ' ' & Lessors.SecondBinding & ' ' & Lessors.RepresentorFirstName & ' ' & Lessors.RepresentorMiddleName & ' ' & Lessors.RepresentorLastName & ' ' & Lessors.RepresentorTitle FROM Lessors WHERE LeaseNo =" & [LeaseNo],Chr(13) & Chr(10) & Chr(13) & Chr(10)) AS Lessors
FROM Lease;

---------------------------------------------------------------------


Thanks Again,

Mark Foster

 

[mfosterla]

I have another question. Some of the fields that I am selecting are going to be null. As you can see in my query I am inserting a space by using & ' '

Is there a simple way to not add the & ' ' if the field is a null value so I want have spaces in the query.

Thanks,

Mark Foster

 

[dhookom]

Mark,
You might want to take advantage of the fact that concatenating string/null values will differ between using + or &. For instance:
"Red" & " " & "Green" = "Red Green"
"Red" & " " & Null = "Red"

You might want to try replace some of your & with +.

Duane
Hook'D on Access
MS Access MVP