Thread: Last Record
View Single Post
  #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?