#1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Joshann
 
Posts: n/a
Default Last Record

I need to be able to add text after the last (and only last) record when
merging. In other words, I need to be able to use an IF statement to
determine whether or not it's on the last record. If it is, add some text.
Is there any way to do this?
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Graham Mayor
 
Posts: n/a
Default Last Record


If you set a custom docproperty containing the number of records merged then

{ IF{ MergeSeq } = { DocProperty NumRecords } "This is the end of the
file" }

should work.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


Joshann wrote:
I need to be able to add text after the last (and only last) record
when merging. In other words, I need to be able to use an IF
statement to determine whether or not it's on the last record. If it
is, add some text. Is there any way to do this?



  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
 
Posts: n/a
Default Last Record

Justt to add a bit to Graham's message, the difficulty, or course, is that
no standard mal merge field returns the count of records merged, so you
either have to perform the merge (e.g. to an output document) or at least
use the preview facilites to set up the count manually, or use VBA to
determine the record count prior to the merge. Then you can insert the info.
and comparison as Graham suggests.

The only way you have a chance of doing it just using fields is to use a
DATABASE field to return the record count, and that will only work if
a. the data source supports the SQL count() aggregate function - a Word
data source won't, but an Excel or Access data source probably will, as will
SQL Server etc.
b. having a DATABASE field that accesses the same file as the mail merge's
data source file does not cause locking problems (it shouldn't with Excel,
Access, and multi-user server-based systems such as SQL Server)
c. you can get the DATABASE field to work.
d. the DATABASE field is counting exactly the same set of records being
used in the merge. If you allow the user to change the merge data source, or
apply filters to the merge data source, you would also have to modify
(programmatically, I suppose) the SQL code in the DATABASE field so that
the same filter was applied. If you allow the user to select individual
records in the Mail Merge Recipients box, it would become very difficult to
get this right.
e. you do not run into a nasty problem with the DATABASE field that seems
to have been introduced in recent releases of Word 2002/2003.

To insert a suitable database field, let's suppose your data source is a
table called mytable in an Access database called mydb.mdb

Enable the database toolbar, and locate and click the Insert database
button. Go through data source selection, select mytable, and ensure you
insert the results as a field. Then click Alt-F9 to show the field code, and
you will see a field along the lines of

{ DATABASE \d "c:\\mydbs\\mydb.mdb" \c "loads of connection info..." \s
"SELECT * FROM `mytable`" \h }

Remove the \h and modify the SQL so it says "SELECT count(*) FROM `mytable`"

When you select the field and press F9, the result (toggle with alt-F9 if
necessary) should be the count of records in the table. If not, there is
probably a syntax error in either the \c parameter or the \s parameter which
you will need to find and correct.

To use this result, you would follow Graham's pattern and do something like

{ IF { MERGESEQ } = { your DATABASE field } "last record in merge" "" }

Gotchas include:
{ MERGESEQ } does not return the number of merge fields actually merged if
the user made individual selections. But tt might work to your advantage in
this case
A nasty change in recent SPs of Word 2002/2003 means that a DATABASE field
result may include a paragraph mark after the record count., which may make
it difficult to make the comparison correctly. You might be able to use

{ IF "{ MERGESEQ }
" = "{ your DATABASE field }" "last record in merge" "" }

but the real problem is that theeffect is unpredictable.

Anyway, it may be worth a try.

Peter Jamieson

To try it, try enabling the Database toolbar and insert
to do it is to insert a DATABASE field that counts the records being merged,
which may or may not be feasible depending on the data source. If the data
source supports a dialect of

"Joshann" wrote in message
...
I need to be able to add text after the last (and only last) record when
merging. In other words, I need to be able to use an IF statement to
determine whether or not it's on the last record. If it is, add some
text.
Is there any way to do this?



  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Joshann
 
Posts: n/a
Default Last Record

Thank you Peter and Graham. I will try this and let you know if it works.
By the way, the data source is an Access database, and the user has no chance
to select which records are being used.

"Peter Jamieson" wrote:

Justt to add a bit to Graham's message, the difficulty, or course, is that
no standard mal merge field returns the count of records merged, so you
either have to perform the merge (e.g. to an output document) or at least
use the preview facilites to set up the count manually, or use VBA to
determine the record count prior to the merge. Then you can insert the info.
and comparison as Graham suggests.

The only way you have a chance of doing it just using fields is to use a
DATABASE field to return the record count, and that will only work if
a. the data source supports the SQL count() aggregate function - a Word
data source won't, but an Excel or Access data source probably will, as will
SQL Server etc.
b. having a DATABASE field that accesses the same file as the mail merge's
data source file does not cause locking problems (it shouldn't with Excel,
Access, and multi-user server-based systems such as SQL Server)
c. you can get the DATABASE field to work.
d. the DATABASE field is counting exactly the same set of records being
used in the merge. If you allow the user to change the merge data source, or
apply filters to the merge data source, you would also have to modify
(programmatically, I suppose) the SQL code in the DATABASE field so that
the same filter was applied. If you allow the user to select individual
records in the Mail Merge Recipients box, it would become very difficult to
get this right.
e. you do not run into a nasty problem with the DATABASE field that seems
to have been introduced in recent releases of Word 2002/2003.

To insert a suitable database field, let's suppose your data source is a
table called mytable in an Access database called mydb.mdb

Enable the database toolbar, and locate and click the Insert database
button. Go through data source selection, select mytable, and ensure you
insert the results as a field. Then click Alt-F9 to show the field code, and
you will see a field along the lines of

{ DATABASE \d "c:\\mydbs\\mydb.mdb" \c "loads of connection info..." \s
"SELECT * FROM `mytable`" \h }

Remove the \h and modify the SQL so it says "SELECT count(*) FROM `mytable`"

When you select the field and press F9, the result (toggle with alt-F9 if
necessary) should be the count of records in the table. If not, there is
probably a syntax error in either the \c parameter or the \s parameter which
you will need to find and correct.

To use this result, you would follow Graham's pattern and do something like

{ IF { MERGESEQ } = { your DATABASE field } "last record in merge" "" }

Gotchas include:
{ MERGESEQ } does not return the number of merge fields actually merged if
the user made individual selections. But tt might work to your advantage in
this case
A nasty change in recent SPs of Word 2002/2003 means that a DATABASE field
result may include a paragraph mark after the record count., which may make
it difficult to make the comparison correctly. You might be able to use

{ IF "{ MERGESEQ }
" = "{ your DATABASE field }" "last record in merge" "" }

but the real problem is that theeffect is unpredictable.

Anyway, it may be worth a try.

Peter Jamieson

To try it, try enabling the Database toolbar and insert
to do it is to insert a DATABASE field that counts the records being merged,
which may or may not be feasible depending on the data source. If the data
source supports a dialect of

"Joshann" wrote in message
...
I need to be able to add text after the last (and only last) record when
merging. In other words, I need to be able to use an IF statement to
determine whether or not it's on the last record. If it is, add some
text.
Is there any way to do this?




  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Joshann
 
Posts: n/a
Default Last Record

I got it to work by creating a "dummy" record at the end of the table in
Access. Then I used an IF statement to test for the value of the dummy
record.

"Peter Jamieson" wrote:

Justt to add a bit to Graham's message, the difficulty, or course, is that
no standard mal merge field returns the count of records merged, so you
either have to perform the merge (e.g. to an output document) or at least
use the preview facilites to set up the count manually, or use VBA to
determine the record count prior to the merge. Then you can insert the info.
and comparison as Graham suggests.

The only way you have a chance of doing it just using fields is to use a
DATABASE field to return the record count, and that will only work if
a. the data source supports the SQL count() aggregate function - a Word
data source won't, but an Excel or Access data source probably will, as will
SQL Server etc.
b. having a DATABASE field that accesses the same file as the mail merge's
data source file does not cause locking problems (it shouldn't with Excel,
Access, and multi-user server-based systems such as SQL Server)
c. you can get the DATABASE field to work.
d. the DATABASE field is counting exactly the same set of records being
used in the merge. If you allow the user to change the merge data source, or
apply filters to the merge data source, you would also have to modify
(programmatically, I suppose) the SQL code in the DATABASE field so that
the same filter was applied. If you allow the user to select individual
records in the Mail Merge Recipients box, it would become very difficult to
get this right.
e. you do not run into a nasty problem with the DATABASE field that seems
to have been introduced in recent releases of Word 2002/2003.

To insert a suitable database field, let's suppose your data source is a
table called mytable in an Access database called mydb.mdb

Enable the database toolbar, and locate and click the Insert database
button. Go through data source selection, select mytable, and ensure you
insert the results as a field. Then click Alt-F9 to show the field code, and
you will see a field along the lines of

{ DATABASE \d "c:\\mydbs\\mydb.mdb" \c "loads of connection info..." \s
"SELECT * FROM `mytable`" \h }

Remove the \h and modify the SQL so it says "SELECT count(*) FROM `mytable`"

When you select the field and press F9, the result (toggle with alt-F9 if
necessary) should be the count of records in the table. If not, there is
probably a syntax error in either the \c parameter or the \s parameter which
you will need to find and correct.

To use this result, you would follow Graham's pattern and do something like

{ IF { MERGESEQ } = { your DATABASE field } "last record in merge" "" }

Gotchas include:
{ MERGESEQ } does not return the number of merge fields actually merged if
the user made individual selections. But tt might work to your advantage in
this case
A nasty change in recent SPs of Word 2002/2003 means that a DATABASE field
result may include a paragraph mark after the record count., which may make
it difficult to make the comparison correctly. You might be able to use

{ IF "{ MERGESEQ }
" = "{ your DATABASE field }" "last record in merge" "" }

but the real problem is that theeffect is unpredictable.

Anyway, it may be worth a try.

Peter Jamieson

To try it, try enabling the Database toolbar and insert
to do it is to insert a DATABASE field that counts the records being merged,
which may or may not be feasible depending on the data source. If the data
source supports a dialect of

"Joshann" wrote in message
...
I need to be able to add text after the last (and only last) record when
merging. In other words, I need to be able to use an IF statement to
determine whether or not it's on the last record. If it is, add some
text.
Is there any way to do this?




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help on Mail Merge just only Show One Record Alan Tang Mailmerge 2 February 21st 06 10:57 AM
"Next Record If", Mail Merge Issues [email protected] Mailmerge 8 February 8th 06 07:11 PM
Next Record If, problems with merging multiple records [email protected] Mailmerge 0 January 13th 06 04:38 PM
Conditional record merging not working in Word 2003 Carey G Mailmerge 2 January 9th 06 07:31 PM
Record 21 was an empty record Ima Mailmerge 0 September 15th 05 06:16 PM


All times are GMT +1. The time now is 02:26 AM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright İ2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"