how do I stop unwanted entries to filter in mail merge?

[John]

When filtering my Excel DB for envelopes, I choose a column and put "is not
equal to" "0"; it works fine. Then I add an "and" 2nd option to look at a
different column for not equal to No. When I choose OK, I get more envelopes
than I had with only the one search argument. A return to filter shows that
a 3rd condition has been added, 2nd column is blank. Nothing I've tried will
get rid of the automatic entry.

What am I doing wrong?

Another problem I had with this MM is the address block; Word would not
match my 5+4 Zip code to their Postal code. I finally got rid of my +4 so I
could proceed. Ideas?

 

[Doug Robbins - Word MVP]

Set up a column in Excel with a formula that returns a specific value for
the combination that you want to filter on and then filter on that column.

Forget about using the AddressBlock and just insert the mergefields that you
want to use in the configuration that you want them.

Also , see "Formatting Word fields with switches" on fellow MVP Graham
Mayor's website at:

http://www.gmayor.com/formatting_word_fields.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[Paullyie]

Hi Doug,

Not sure if this is directly the same, But I have a similar issue
Using office 2007, I have a letter mail merge in Word looking to Excel DB.
One of my filters is set to
FIELD: Contact Name
COMPARISON: 'IS BLANK'

This filter does not apply correctly.
It returns all rows whether Contact Name is Blank or contains a value.

Have trawled through various forums, not many answers, but few forums have
mentioned this is a bug with Word 2007.

Like above I was able to make a workaround and populate the Blank fileds
with a single value, and filter based on that value.

Just wondering is there a fix for this?

Thanks in Advance
Paul
-----------------------------------------------------------------------

 

[Peter Jamieson]

If using VBA does not cause problems for you, can you try setting up
your data source using the following code:

Sub setupdatasource()
Dim strDSPath as String
' Put your Excel sheet's pathname in here:
strDSPath = "c:\myxls\myxl.xlsx"
With ActiveDocument.MailMerge
' uncomment the next line if necessary
'.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:=strDSPath, _
sqlstatement:=" SELECT * FROM [Sheet1$]" & _
" WHERE [Contact Name] is null OR [Contact Name] = ''"
End With

End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Paullyie]

Hi Peter,

Thanks for the reply, unfortunatly I am not using VB,
Basically what I am doing is re-creating a Mail merge that was set up in an
old version of office (office 95 to be precise). It is being moved to Office
2007
A Word Letter Template merged to an Excel database
using the simple
The functions and setup are pretty much the same.
http://i713.photobucket.com/albums/ww139/paullyie82/Word_Merge.jpg
I am using the standed Filter that is part of the Mail Merge Wizard.
(See Screen Shot Link) - I set up an example for this.

But when I select the the funtion 'Is Blank' - the filter/result does not
apply.
It will still print all seven results

Any ideas,

Thanks again for the help,
Paul

If using VBA does not cause problems for you, can you try setting up
your data source using the following code:

Sub setupdatasource()
Dim strDSPath as String
' Put your Excel sheet's pathname in here:
strDSPath = "c:\myxls\myxl.xlsx"
With ActiveDocument.MailMerge
' uncomment the next line if necessary
'.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:=strDSPath, _
sqlstatement:=" SELECT * FROM [Sheet1$]" & _
" WHERE [Contact Name] is null OR [Contact Name] = ''"
End With

End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

Hi Doug,

Not sure if this is directly the same, But I have a similar issue
Using office 2007, I have a letter mail merge in Word looking to Excel DB.
One of my filters is set to
FIELD: Contact Name
COMPARISON: 'IS BLANK'

This filter does not apply correctly.
It returns all rows whether Contact Name is Blank or contains a value.

Have trawled through various forums, not many answers, but few forums have
mentioned this is a bug with Word 2007.

Like above I was able to make a workaround and populate the Blank fileds
with a single value, and filter based on that value.

Just wondering is there a fix for this?

Thanks in Advance
Paul

 

[Peter Jamieson]

Yes, unfortunately there can be problems in this area - you may notice
for example that when you close/reopen the document, the filter
condition may be repeated.

As long as your merge is reasonable simple, you may also be able to work
around this by inserting a { SKIPIF } field at the beginning of your
document, e.g.

{ SKIPIF "{MERGEFIELD "Contact name" }" <> "" }

where all the {} are the special field code braces you can enter using
ctrl-F9.

I'd make a couple of other suggestions but I am away for a couple of
days so maybe someone else will chip in. Meanwhile, if you can possibly
try the approach I suggested (it's a one-off for a given data source) -
for material on how to run VBA code, you can try Graham Mayor's page at

http://www.gmayor.com/installing_macro.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Hi Peter,

Thanks for the reply, unfortunatly I am not using VB,
Basically what I am doing is re-creating a Mail merge that was set up in an
old version of office (office 95 to be precise). It is being moved to Office
2007
A Word Letter Template merged to an Excel database
using the simple
The functions and setup are pretty much the same.
http://i713.photobucket.com/albums/ww139/paullyie82/Word_Merge.jpg
I am using the standed Filter that is part of the Mail Merge Wizard.
(See Screen Shot Link) - I set up an example for this.

But when I select the the funtion 'Is Blank' - the filter/result does not
apply.
It will still print all seven results

Any ideas,

Thanks again for the help,
Paul

If using VBA does not cause problems for you, can you try setting up
your data source using the following code:

Sub setupdatasource()
Dim strDSPath as String
' Put your Excel sheet's pathname in here:
strDSPath = "c:\myxls\myxl.xlsx"
With ActiveDocument.MailMerge
' uncomment the next line if necessary
'.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:=strDSPath, _
sqlstatement:=" SELECT * FROM [Sheet1$]" & _
" WHERE [Contact Name] is null OR [Contact Name] = ''"
End With

End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

Hi Doug,

Not sure if this is directly the same, But I have a similar issue
Using office 2007, I have a letter mail merge in Word looking to Excel DB.
One of my filters is set to
FIELD: Contact Name
COMPARISON: 'IS BLANK'

This filter does not apply correctly.
It returns all rows whether Contact Name is Blank or contains a value.

Have trawled through various forums, not many answers, but few forums have
mentioned this is a bug with Word 2007.

Like above I was able to make a workaround and populate the Blank fileds
with a single value, and filter based on that value.

Just wondering is there a fix for this?

Thanks in Advance
Paul
-----------------------------------------------------------------------
:

Set up a column in Excel with a formula that returns a specific value for
the combination that you want to filter on and then filter on that column.

Forget about using the AddressBlock and just insert the mergefields that you
want to use in the configuration that you want them.

Also , see "Formatting Word fields with switches" on fellow MVP Graham
Mayor's website at:

http://www.gmayor.com/formatting_word_fields.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
When filtering my Excel DB for envelopes, I choose a column and put "is
not
equal to" "0"; it works fine. Then I add an "and" 2nd option to look at a
different column for not equal to No. When I choose OK, I get more
envelopes
than I had with only the one search argument. A return to filter shows
that
a 3rd condition has been added, 2nd column is blank. Nothing I've tried
will
get rid of the automatic entry.

What am I doing wrong?

Another problem I had with this MM is the address block; Word would not
match my 5+4 Zip code to their Postal code. I finally got rid of my +4 so
I
could proceed. Ideas?

 

[Paullyie]

Thanks for all teh helpp Peter,
I will give your siggestion a try,

I was able to use a work-around, although not ideal.
it works fine if I populate the Blank fields with a value, and filter where
equal to that value - this works fine,

I'll give it a go and let you know,

Thanks again,
Paul

Yes, unfortunately there can be problems in this area - you may notice
for example that when you close/reopen the document, the filter
condition may be repeated.

As long as your merge is reasonable simple, you may also be able to work
around this by inserting a { SKIPIF } field at the beginning of your
document, e.g.

{ SKIPIF "{MERGEFIELD "Contact name" }" <> "" }

where all the {} are the special field code braces you can enter using
ctrl-F9.

I'd make a couple of other suggestions but I am away for a couple of
days so maybe someone else will chip in. Meanwhile, if you can possibly
try the approach I suggested (it's a one-off for a given data source) -
for material on how to run VBA code, you can try Graham Mayor's page at

http://www.gmayor.com/installing_macro.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Hi Peter,

Thanks for the reply, unfortunatly I am not using VB,
Basically what I am doing is re-creating a Mail merge that was set up in an
old version of office (office 95 to be precise). It is being moved to Office
2007
A Word Letter Template merged to an Excel database
using the simple
The functions and setup are pretty much the same.
http://i713.photobucket.com/albums/ww139/paullyie82/Word_Merge.jpg
I am using the standed Filter that is part of the Mail Merge Wizard.
(See Screen Shot Link) - I set up an example for this.

But when I select the the funtion 'Is Blank' - the filter/result does not
apply.
It will still print all seven results

Any ideas,

Thanks again for the help,
Paul

If using VBA does not cause problems for you, can you try setting up
your data source using the following code:

Sub setupdatasource()
Dim strDSPath as String
' Put your Excel sheet's pathname in here:
strDSPath = "c:\myxls\myxl.xlsx"
With ActiveDocument.MailMerge
' uncomment the next line if necessary
'.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:=strDSPath, _
sqlstatement:=" SELECT * FROM [Sheet1$]" & _
" WHERE [Contact Name] is null OR [Contact Name] = ''"
End With

End Sub

Peter Jamieson

http://tips.pjmsn.me.uk

Paullyie wrote:
Hi Doug,

Not sure if this is directly the same, But I have a similar issue
Using office 2007, I have a letter mail merge in Word looking to Excel DB.
One of my filters is set to
FIELD: Contact Name
COMPARISON: 'IS BLANK'

This filter does not apply correctly.
It returns all rows whether Contact Name is Blank or contains a value.

Have trawled through various forums, not many answers, but few forums have
mentioned this is a bug with Word 2007.

Like above I was able to make a workaround and populate the Blank fileds
with a single value, and filter based on that value.

Just wondering is there a fix for this?

Thanks in Advance
Paul
-----------------------------------------------------------------------
:

Set up a column in Excel with a formula that returns a specific value for
the combination that you want to filter on and then filter on that column.

Forget about using the AddressBlock and just insert the mergefields that you
want to use in the configuration that you want them.

Also , see "Formatting Word fields with switches" on fellow MVP Graham
Mayor's website at:

http://www.gmayor.com/formatting_word_fields.htm


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
When filtering my Excel DB for envelopes, I choose a column and put "is
not
equal to" "0"; it works fine. Then I add an "and" 2nd option to look at a
different column for not equal to No. When I choose OK, I get more
envelopes
than I had with only the one search argument. A return to filter shows
that
a 3rd condition has been added, 2nd column is blank. Nothing I've tried
will
get rid of the automatic entry.

What am I doing wrong?

Another problem I had with this MM is the address block; Word would not
match my 5+4 Zip code to their Postal code. I finally got rid of my +4 so
I
could proceed. Ideas?