suppressing part of a text field in a merged document

[Thicklizzie]

I am mail merging from an Access database and one of the fields contains full
names (e.g. Mary Smith, Michael Jones, etc.) I want the merged document to
show just the first name (e.g. Dear Mary or Dear Michael).

I cannot change how the information is entered in the Access database and
this information is already change by a "Mid([ReferalName],11,55)" in the
query.

 

[macropod]

Hi Thicklizzie,

It isn't possible to suppress part of a maergefield. Your database really should have the surnames and given names in separate
fields.

 

[Peter Jamieson]

<<
I cannot change how the information is entered in the Access database
and this information is already change by a "Mid([ReferalName],11,55)"
in the query.
If you can use Word VBA, you may be able to create the query you need
even if you cannot change the Access database that you need to use.

For example, suppose your merge currently uses an Access query called
myquery as the data source, and myquery is defined as

SELECT fielda, mid([ReferalName],11,55) As rname from mytable

Suppose you actually need the part of "rname" up to the first space, if any.

Then you would need something like

SELECT fielda, left(rname & ' ',instr(1,rname & ' ',' ')-1) as myrname
from myquery

(In other words, you can use SQL queries to query a query, not just a table)

To do that, you would need to run VBA such as

Activedocument.MailMerge.OpenDataSource _
Name:="the full pathname of your Access .mdb file", _
SQLStatement:= _
" SELECT" & _
" fielda," & _
" left(rname & ' '," & _
" instr(1,rname & ' ',' ')-1)" & _
" as myrname" & _
" FROM myquery"

(You should only need to run this once for the query to be set up as
your data source, and there may well be complications if for example
users of the document try to change the query using the facilities in
Edit Recipients )

I can't guarantee that that will work in all versions of Word but it
functions in Word 2003

Peter Jamieson

http://tips.pjmsn.me.uk

I am mail merging from an Access database and one of the fields contains full
names (e.g. Mary Smith, Michael Jones, etc.) I want the merged document to
show just the first name (e.g. Dear Mary or Dear Michael).

I cannot change how the information is entered in the Access database and
this information is already change by a "Mid([ReferalName],11,55)" in the
query.

 

[Thicklizzie]

Thank you for your help. Unfortunately I don't know VBA or SQL at all and
would probably crash my whole computer if I tried to do any of that stuff.
But thank you so much for coming up with, I am sure, a very workable solution.

<<
I cannot change how the information is entered in the Access database
and this information is already change by a "Mid([ReferalName],11,55)"
in the query.
If you can use Word VBA, you may be able to create the query you need
even if you cannot change the Access database that you need to use.

For example, suppose your merge currently uses an Access query called
myquery as the data source, and myquery is defined as

SELECT fielda, mid([ReferalName],11,55) As rname from mytable

Suppose you actually need the part of "rname" up to the first space, if any.

Then you would need something like

SELECT fielda, left(rname & ' ',instr(1,rname & ' ',' ')-1) as myrname
from myquery

(In other words, you can use SQL queries to query a query, not just a table)

To do that, you would need to run VBA such as

Activedocument.MailMerge.OpenDataSource _
Name:="the full pathname of your Access .mdb file", _
SQLStatement:= _
" SELECT" & _
" fielda," & _
" left(rname & ' '," & _
" instr(1,rname & ' ',' ')-1)" & _
" as myrname" & _
" FROM myquery"

(You should only need to run this once for the query to be set up as
your data source, and there may well be complications if for example
users of the document try to change the query using the facilities in
Edit Recipients )

I can't guarantee that that will work in all versions of Word but it
functions in Word 2003

Peter Jamieson

http://tips.pjmsn.me.uk

I am mail merging from an Access database and one of the fields contains full
names (e.g. Mary Smith, Michael Jones, etc.) I want the merged document to
show just the first name (e.g. Dear Mary or Dear Michael).

I cannot change how the information is entered in the Access database and
this information is already change by a "Mid([ReferalName],11,55)" in the
query.