Mail merge using Access query not working

[mguidry]

We've created a mail merge document that uses an Access 2k query datasource. The query has two input fields that the user must specify once the query is run (StartDate and EndDate). These date fields are not query criteria, and are not table related data - just fields that the user must specify and need to be displayed in the mail merge.

The problem is that the mail merge does not display the information from these input fields, even though the fields are populated within the query. Any ideas?

 

[lespaul]

This doesn't make any sense. You have two inputs that are not used in the criteria of the query but then you go on to say that the information is not displayed even though the fields are populated in the query.

Why don't you post some of your code that creates the mail merge and the query that's the data source and fully explain what's going on.

Leslie

 

[mguidry]

Well - we just re-structured the way that we're doing it. We're making an append query first in order to get the StartDate and EndDate fields into a table. Below is my query in SQL view, with the fields in question in red:

SELECT tblCustomers.CustStatus, tblGenerators.Generator, tblGenerators.[EPAID#], tblGenerators.[AssocCustID#], tblGenerators.[Route#], tblGenerators.GenContact, tblGenerators.RoutePhone, tblGenerators.RouteAltPhone, tblGenerators.RouteMobilePhone, tblGenerators.RouteFax, [Route Begin Date] AS BeginDate, [Route End Date] AS EndDate
FROM tblCustomers INNER JOIN tblGenerators ON tblCustomers.[CustID#] = tblGenerators.[AssocCustID#]
WHERE (((tblCustomers.CustStatus)="1") AND ((tblGenerators.[Route#])=[Forms]![frmTransRouteSchedule]![Text0]) AND ((tblGenerators.RouteEmail) Is Null));

The code that runs the query doesn't create a mail merge - it calls a word mail merge document that is linked to the query above as it's data source. Because the date fields in question are not derived from a table, the user has to enter the dates when the query is run. The merged document, however, will not display these dates.

Hope that clarifies things, and thanks for the reply.

 

[lespaul]

Ok, if you are calling a word mail merge document from an Access form (which is what I'm guessing since the parameters for the query are coming from frmTransRouteSchedule) then once the word document is open you can just plug in the information directly from frmTransRouteSchedule!Text0.

I'm not sure of the exact syntax, but something like:

Word.ActiveDocument.FormFields.Item("Date1Field").Result:= frmTransRouteSchedule!Text0

Leslie

 

[mguidry]

I'll pass that code example on to my VB guy. Thanks for the assistance.