Reply
 
Thread Tools Display Modes
  #1   Report Post  
HiramPat
 
Posts: n/a
Default Mail Merge - Next Record If Statement

We are trying to run a mail merge where we only want it to go to the next
record if the ID number on the next record is DIFFERENT from the ID number on
the current record. If the ID numbers are the same, we want to continue on
the same document.

Can anyone help me write a correct Next Record If statement to accomplish
this?

Thanks,
Pat
  #2   Report Post  
Doug Robbins
 
Posts: n/a
Default

Sounds like you are trying to create a multiple items per condition type
document, which Word does not really have the ability to perform using
mailmerge.


See the "Multiple items per condition" item under the "Special merges"
section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindy...r/MergFram.htm

Or, if you create a Catalog (on in Word XP and later, it's called Directory)
type mailmerge main document with the mergefields in the cells of a one row
table in the mailmerge main document with the keyfield in the first cell in
the row and then execute that merge to a new document and then run the
following macro, it will create separate tables with the records for each
key field in them. With a bit of further development, you may be able to
get it to do what you want.

' Macro to create multiple items per condition in separate tables from a
directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i



--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"HiramPat" wrote in message
...
We are trying to run a mail merge where we only want it to go to the next
record if the ID number on the next record is DIFFERENT from the ID number
on
the current record. If the ID numbers are the same, we want to continue
on
the same document.

Can anyone help me write a correct Next Record If statement to accomplish
this?

Thanks,
Pat



  #3   Report Post  
Radar2000
 
Posts: n/a
Default

This maybe a silly question, but I've gone to the Microsoft Knowledge Base
article Q105888 site that was suggested. It looks like this logic would work
for me, but I don't know how to copy the mergefield solution into Word. I've
created the sample data source in Excel.

Any help would be appreciated.

Thanks.

"Doug Robbins" wrote:

Sounds like you are trying to create a multiple items per condition type
document, which Word does not really have the ability to perform using
mailmerge.


See the "Multiple items per condition" item under the "Special merges"
section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindy...r/MergFram.htm

Or, if you create a Catalog (on in Word XP and later, it's called Directory)
type mailmerge main document with the mergefields in the cells of a one row
table in the mailmerge main document with the keyfield in the first cell in
the row and then execute that merge to a new document and then run the
following macro, it will create separate tables with the records for each
key field in them. With a bit of further development, you may be able to
get it to do what you want.

' Macro to create multiple items per condition in separate tables from a
directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As Range
Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i



--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"HiramPat" wrote in message
...
We are trying to run a mail merge where we only want it to go to the next
record if the ID number on the next record is DIFFERENT from the ID number
on
the current record. If the ID numbers are the same, we want to continue
on
the same document.

Can anyone help me write a correct Next Record If statement to accomplish
this?

Thanks,
Pat




  #4   Report Post  
Graham Mayor
 
Posts: n/a
Default

What I think you are asking is how to create the field structure in Word? If
this is correct then you type the information in the manner shown, using
CTRL+F9 for each pair of field brackets {}.

--

Graham Mayor - Word MVP

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




Radar2000 wrote:
This maybe a silly question, but I've gone to the Microsoft Knowledge
Base article Q105888 site that was suggested. It looks like this
logic would work for me, but I don't know how to copy the mergefield
solution into Word. I've created the sample data source in Excel.

Any help would be appreciated.

Thanks.

"Doug Robbins" wrote:

Sounds like you are trying to create a multiple items per condition
type document, which Word does not really have the ability to
perform using mailmerge.


See the "Multiple items per condition" item under the "Special
merges" section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindy...r/MergFram.htm

Or, if you create a Catalog (on in Word XP and later, it's called
Directory) type mailmerge main document with the mergefields in the
cells of a one row table in the mailmerge main document with the
keyfield in the first cell in the row and then execute that merge to
a new document and then run the following macro, it will create
separate tables with the records for each key field in them. With a
bit of further development, you may be able to get it to do what you
want.

' Macro to create multiple items per condition in separate tables
from a directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As
Range Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore =
False For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i



--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on
a paid consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"HiramPat" wrote in message
...
We are trying to run a mail merge where we only want it to go to
the next record if the ID number on the next record is DIFFERENT
from the ID number on
the current record. If the ID numbers are the same, we want to
continue on
the same document.

Can anyone help me write a correct Next Record If statement to
accomplish this?

Thanks,
Pat



  #5   Report Post  
Radar2000
 
Posts: n/a
Default

Thanks for your reply Graham.

I understand the whole CTRL+F9 bracket logic, but is there a way that I can
select a single bracket? Looking at the Microsoft Knowledge Base article,
they show single (open or closed) brackets in their solution??? I can't seem
to duplicate this in Word.

Is there a more efficient way of copying this knowledge base formula, or do
I need to type everything out?

Thank you.


"Graham Mayor" wrote:

What I think you are asking is how to create the field structure in Word? If
this is correct then you type the information in the manner shown, using
CTRL+F9 for each pair of field brackets {}.

--

Graham Mayor - Word MVP

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




Radar2000 wrote:
This maybe a silly question, but I've gone to the Microsoft Knowledge
Base article Q105888 site that was suggested. It looks like this
logic would work for me, but I don't know how to copy the mergefield
solution into Word. I've created the sample data source in Excel.

Any help would be appreciated.

Thanks.

"Doug Robbins" wrote:

Sounds like you are trying to create a multiple items per condition
type document, which Word does not really have the ability to
perform using mailmerge.


See the "Multiple items per condition" item under the "Special
merges" section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindy...r/MergFram.htm

Or, if you create a Catalog (on in Word XP and later, it's called
Directory) type mailmerge main document with the mergefields in the
cells of a one row table in the mailmerge main document with the
keyfield in the first cell in the row and then execute that merge to
a new document and then run the following macro, it will create
separate tables with the records for each key field in them. With a
bit of further development, you may be able to get it to do what you
want.

' Macro to create multiple items per condition in separate tables
from a directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As
Range Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore =
False For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i



--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on
a paid consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"HiramPat" wrote in message
...
We are trying to run a mail merge where we only want it to go to
the next record if the ID number on the next record is DIFFERENT
from the ID number on
the current record. If the ID numbers are the same, we want to
continue on
the same document.

Can anyone help me write a correct Next Record If statement to
accomplish this?

Thanks,
Pat






  #6   Report Post  
Graham Mayor
 
Posts: n/a
Default

There are no single brackets in the formula - they are *always* in pairs.
The closing bracket may not be on the same line as the opening bracket, but
it will be there - and no, there is no simple way to make fields from a
listing.

--

Graham Mayor - Word MVP

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




Radar2000 wrote:
Thanks for your reply Graham.

I understand the whole CTRL+F9 bracket logic, but is there a way that
I can select a single bracket? Looking at the Microsoft Knowledge
Base article, they show single (open or closed) brackets in their
solution??? I can't seem to duplicate this in Word.

Is there a more efficient way of copying this knowledge base formula,
or do I need to type everything out?

Thank you.


"Graham Mayor" wrote:

What I think you are asking is how to create the field structure in
Word? If this is correct then you type the information in the manner
shown, using CTRL+F9 for each pair of field brackets {}.

--

Graham Mayor - Word MVP

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




Radar2000 wrote:
This maybe a silly question, but I've gone to the Microsoft
Knowledge Base article Q105888 site that was suggested. It looks
like this logic would work for me, but I don't know how to copy the
mergefield solution into Word. I've created the sample data source
in Excel.

Any help would be appreciated.

Thanks.

"Doug Robbins" wrote:

Sounds like you are trying to create a multiple items per condition
type document, which Word does not really have the ability to
perform using mailmerge.


See the "Multiple items per condition" item under the "Special
merges" section of fellow MVP Cindy Meister's website at

http://homepage.swissonline.ch/cindy...r/MergFram.htm

Or, if you create a Catalog (on in Word XP and later, it's called
Directory) type mailmerge main document with the mergefields in the
cells of a one row table in the mailmerge main document with the
keyfield in the first cell in the row and then execute that merge
to a new document and then run the following macro, it will create
separate tables with the records for each key field in them. With
a bit of further development, you may be able to get it to do what
you want.

' Macro to create multiple items per condition in separate tables
from a directory type mailmerge

Dim source As Document, target As Document, scat As Range, tcat As
Range Dim data As Range, stab As Table, ttab As Table
Dim i As Long, j As Long, k As Long, n As Long
Set source = ActiveDocument
Set target = Documents.Add
Set stab = source.Tables(1)
k = stab.Columns.Count
Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1,
numcolumns:=k - 1)
Set scat = stab.Cell(1, 1).Range
scat.End = scat.End - 1
ttab.Cell(1, 1).Range = scat
j = ttab.Rows.Count
For i = 1 To stab.Rows.Count
Set tcat = ttab.Cell(j, 1).Range
tcat.End = tcat.End - 1
Set scat = stab.Cell(i, 1).Range
scat.End = scat.End - 1
If scat tcat Then
ttab.Rows.Add
j = ttab.Rows.Count
ttab.Cell(j, 1).Range = scat
ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True
ttab.Rows.Add
ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore =
False For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
Else
ttab.Rows.Add
For n = 2 To k
Set data = stab.Cell(i, n).Range
data.End = data.End - 1
ttab.Cell(ttab.Rows.Count, n - 1).Range = data
Next n
End If
Next i



--
Please respond to the Newsgroup for the benefit of others who may
be interested. Questions sent directly to me will only be
answered on a paid consulting basis.

Hope this helps,
Doug Robbins - Word MVP
"HiramPat" wrote in message
...
We are trying to run a mail merge where we only want it to go to
the next record if the ID number on the next record is DIFFERENT
from the ID number on
the current record. If the ID numbers are the same, we want to
continue on
the same document.

Can anyone help me write a correct Next Record If statement to
accomplish this?

Thanks,
Pat



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
mail merge with attachments AS Mailmerge 5 April 9th 05 09:49 AM
How to choose your own record deliminators in mail merge? GMed Mailmerge 0 January 21st 05 04:43 PM
Going To A Previous Record in Mail Merge KristoferMc Mailmerge 4 January 17th 05 12:29 PM
How do I keep a blank line from printing in a mail merge? Record. Kellye Mailmerge 1 December 8th 04 01:50 AM
Using Hyperlinks in Mail Merge IF...THEN...ELSE Statements Mark V Mailmerge 8 November 30th 04 02:31 PM


All times are GMT +1. The time now is 02:55 PM.

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"