VB + Access - Merge to Word

I

irobot8

I'm building a contacts database in VB, with an Access back end.

WRT table structure, each contact undertakes many activities. There
are 3 main tables:

1. tbContacts (stores contact details e.g. Name, Address, Tel No etc.)
2. tbActivityBridge (which details the activities each contact has
undertaken, containing Activity ID and Contact ID only)
3. tbActivities (with details of each individual activity e.g. Code,
Description, Location etc.)

I'm providing the user with a form within the VB front-end which
enables them to choose up to 5 activity codes, in order that they can
produce labels in MS Word for contacts that have undertaken specified
activities. The VB I've used is:

MyMailMerge.OpenDataSource Name:=strConnection, _
LinkToSource:=True, SQLStatement:=strQuery,
SQLStatement1:=strCriteria, _
SubType:=wdMergeSubTypeWord2000

The SQL string I've used for strCriteria is dynamically generated
according to what the user enters in the VB form for activity codes.
The strQuery string is fixed, but overruns the 255 character limit
imposed by Word. The SQL is as follows:

strQuery = "SELECT tbContacts.Contact_Title,
tbContacts.Contact_First_Name, tbContacts.Contact_Surname,
tbContacts.Contact_Address_1, tbContacts.Contact_Address_2,
tbContacts.Contact_Town, tbContacts.Contact_County_ID,
tbContacts.Contact_Postcode
FROM tbContacts INNER JOIN (tbActivities INNER JOIN tbActivityBridge
ON tbActivities.Activity_ID = tbActivityBridge.Activity_ID) ON
tbContacts.Contact_ID = tbActivityBridge.Contact_ID
GROUP BY tbContacts.Contact_Title, tbContacts.Contact_First_Name,
tbContacts.Contact_Surname, tbContacts.Contact_Address_1,
tbContacts.Contact_Address_2, tbContacts.Contact_Town,
tbContacts.Contact_County_ID, tbContacts.Contact_Postcode "

strCriteria = "HAVING (((Min([tbActivities].[Activity_Code]) Like '*"
& strActivityCode1 "*')=True)) AND
(((Min([tbActivities].[Activity_Code]) Like '*" & strActivityCode2 &
"*')=True));"


The strQuery string is over 600 characters!!

Can anyone suggest a way of reducing this, so that I can use the above
method with sqlStatement and sqlStatement1. Or perhaps there is
another way using queryDefs, stored queries, sub-queries et al.

If I use * within the select statement, I get a 'Cannot have aggregate
function in WHERE clause' errors, or 'You tried to execute a query
that does not include the specified expression Contact_ID as part of
an aggregate function'.

Any help greatly appreciated.

irobot8
 
C

Cindy M -WordMVP-

Hi Irobot8,

The first thing to note is that Word's mail merge cannot link to
aggregate-type queries. So you will need to use resort to
creating/modifying a query in the database (using QueryDef).

Then build (or use) a second query that bases on this aggregate query,
simply selecting all (or some of) the fields from the aggregate query.
You can set the criteria here, if you wish, as well.

Or, at this point, the criteria would probably fit within the 512
characters limit for SQLStatement and SQLStatement1, combined. And, at
this point, you'd also be able to use SELECT * FROM...

Note, too, that as long as the field names are unique to a single source
table used in the query, you can usually leave the table names out of
the SELECT and WHERE clauses.
The SQL string I've used for strCriteria is dynamically generated
according to what the user enters in the VB form for activity codes.
The strQuery string is fixed, but overruns the 255 character limit
imposed by Word. The SQL is as follows:

strQuery = "SELECT tbContacts.Contact_Title,
tbContacts.Contact_First_Name, tbContacts.Contact_Surname,
tbContacts.Contact_Address_1, tbContacts.Contact_Address_2,
tbContacts.Contact_Town, tbContacts.Contact_County_ID,
tbContacts.Contact_Postcode
FROM tbContacts INNER JOIN (tbActivities INNER JOIN tbActivityBridge
ON tbActivities.Activity_ID = tbActivityBridge.Activity_ID) ON
tbContacts.Contact_ID = tbActivityBridge.Contact_ID
GROUP BY tbContacts.Contact_Title, tbContacts.Contact_First_Name,
tbContacts.Contact_Surname, tbContacts.Contact_Address_1,
tbContacts.Contact_Address_2, tbContacts.Contact_Town,
tbContacts.Contact_County_ID, tbContacts.Contact_Postcode "

strCriteria = "HAVING (((Min([tbActivities].[Activity_Code]) Like '*"
& strActivityCode1 "*')=True)) AND
(((Min([tbActivities].[Activity_Code]) Like '*" & strActivityCode2 &
"*')=True));"


The strQuery string is over 600 characters!!

Can anyone suggest a way of reducing this, so that I can use the above
method with sqlStatement and sqlStatement1. Or perhaps there is
another way using queryDefs, stored queries, sub-queries et al.

If I use * within the select statement, I get a 'Cannot have aggregate
function in WHERE clause' errors, or 'You tried to execute a query
that does not include the specified expression Contact_ID as part of
an aggregate function'.
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail :)
 
Top