mail merge filter criteria

I supppose that I am not knowledgeable enough to recognize if this has been
addressed before, so I am asking because this is creating serious problems
for me.

AT some point in the last several months an update to my Office 2003 has
been installed that has resulted in an inability to correctly make changes
in existing merge criteris. Specifically, putting in a change results in
nearly every filtering criteria becoming a two-part OR, with the addition of
an unwanted IF about the file dbeing empty/nulll OR my desired inclusion
criteria. We have lots of empty fields all over lots of Excel databases that
get used in over 100 pre-reports, and all of the records with any of the
fields empty get selected because of this unwanted-but-cannot-be-removed OR
that has begun being added.

I am not a knowledgeable user. I pretty much only know how to maintain and
make minor changes to he existing reports. And I cannot even do that because
some update has changed the way things work such that what I ask for gets
changed to be what I definitely don't want.

How has it come about that Mail Merge is deciding -- incorrectly -- for me
that what I really want is to search for empty cells as well as the cells
containing the values we want? And what can I do to stop it?

If this has been answered already somwhere, please accept my apology for my
ignorance that hasn't recognized it, and explain it more simply so that I'll
get it this time.

Thanx, folks...BillG

This has long been a nasty area within Word, but I believe that the
origin of this problem is described in the following article:

http://support.microsoft.com/kb/286880

and that it will occur as soon as you have an OLE DB connection to a
data source and specify "is blank" or perhaps "is not blank" as one of
the criteria in your "Advanced criteria".

NB, the article states that

"
The SQL string for the expression "MyField Is blank" is:
(MyField IS NULL) OR (MyField IS '')
"
which is
a. syntactically incorrect - it should say
(MyField IS NULL) OR (MyField = '')
b. arguable. It all depends on what they mean by "blank". Do they mean
1 has one or more space characters (specifically, ASCII/Unicode
character 32)
2 has one or more "white space characters" (spaces, tabs etc.)
3 has a zero-length string (which in Excel you could insert using
the formula ="")
4 is null (which in Excel appears to correspond to a cell with no
text or formula in it
5 something else?
6 some combination of the above?

The Jet SQL that is used to get data from Excel actually retrieves both
(1) and (3) using Myfield = '', and (4) using myfield IS NULL. So I
guess to cover the ground, Microsoft more or less has to have both
expressions in its SQL. The problem is that Word+ODSO do not work
together properly to maintain the overall expression correctly.

What I cannot understand is how you were OK before. It's either because
a. it /was/ OK before, and there has been a change to the way
Word/ODSO works (I've had trouble tracking this in the past because
there have been several fixes in this general area).
b. you were actually using ODBC (or possibly even DDE) to connect
before, and something has changed that is forcing OLE DB instead. With
ODBC, for example the choice you get is still "is blank", but
a. that translates into

Myfield IS NULL

(and not "OR Myfield = '')
b. Word does not go via ODSO for ODBC connections.

In fact, I find it difficult to imagine that Word could have started
changing connection types without you noticing, but that's all I can
think of right now. Another rather remote possibility is that you were
using the old Excel converter to get data from Excel, and that a Word
update has removed it or rendered it useless.

So, is there anything you can do?

Well,
1. if the connection method has changed, you could try to revert to
the one you were using before. You can check
Word-Tools-Options-General-Confirm conversion at open
to ensure that you are asked for the available connection methods after
you have selected the data source in the Select Data Source dialog box.
2. If you are setting up merges where the filter criteria do not need
to change, you can consider modifying the SQL Query string directly in
Word VBA. As long as you do not need to go back into the Query options
dialog, the SQL that you specify should "stick".

However, if there has been a change, it may be worth contacting
Microsoft directly (the people in here are typically volunteers who do
not work for microsoft) and see if they can shed any light on the matter.

Peter Jamieson

http://tips.pjmsn.me.uk

Bill-at-JSC wrote:
I supppose that I am not knowledgeable enough to recognize if this has been
addressed before, so I am asking because this is creating serious problems
for me.

AT some point in the last several months an update to my Office 2003 has
been installed that has resulted in an inability to correctly make changes
in existing merge criteris. Specifically, putting in a change results in
nearly every filtering criteria becoming a two-part OR, with the addition of
an unwanted IF about the file dbeing empty/nulll OR my desired inclusion
criteria. We have lots of empty fields all over lots of Excel databases that
get used in over 100 pre-reports, and all of the records with any of the
fields empty get selected because of this unwanted-but-cannot-be-removed OR
that has begun being added.

I am not a knowledgeable user. I pretty much only know how to maintain and
make minor changes to he existing reports. And I cannot even do that because
some update has changed the way things work such that what I ask for gets
changed to be what I definitely don't want.

How has it come about that Mail Merge is deciding -- incorrectly -- for me
that what I really want is to search for empty cells as well as the cells
containing the values we want? And what can I do to stop it?

If this has been answered already somwhere, please accept my apology for my
ignorance that hasn't recognized it, and explain it more simply so that I'll
get it this time.

Thanx, folks...BillG

Thanx for explanation. I understood most of it (I don't know what is meant
by any of the accronyms like ODSO and ODBC and so forth) but I think I have
an idea of what is happening to me concerning the interpretations of "blank"
and "null" and "empty" cells.

You're correct that the databases being used by any particular report do
change sometimes, and it is biting us when that connection is getting
re-done, because that is when the filter/sort criteria get re-specified,
which is when the additional OR phrase is being added. In fact, when that
happens I've noticed that the choices for filtering have changed (ex. one can
now specify "contains" as well as "equals").

Anyhow, thanx for the response. I'm still between a rock and a hard place
about what to actually do now that I more-or-less understand, but I can now
at least kinda explain to the-powers-that-be.

"Peter Jamieson" wrote:

This has long been a nasty area within Word, but I believe that the
origin of this problem is described in the following article:

http://support.microsoft.com/kb/286880

and that it will occur as soon as you have an OLE DB connection to a
data source and specify "is blank" or perhaps "is not blank" as one of
the criteria in your "Advanced criteria".

NB, the article states that

"
The SQL string for the expression "MyField Is blank" is:
(MyField IS NULL) OR (MyField IS '')
"
which is
a. syntactically incorrect - it should say
(MyField IS NULL) OR (MyField = '')
b. arguable. It all depends on what they mean by "blank". Do they mean
1 has one or more space characters (specifically, ASCII/Unicode
character 32)
2 has one or more "white space characters" (spaces, tabs etc.)
3 has a zero-length string (which in Excel you could insert using
the formula ="")
4 is null (which in Excel appears to correspond to a cell with no
text or formula in it
5 something else?
6 some combination of the above?

The Jet SQL that is used to get data from Excel actually retrieves both
(1) and (3) using Myfield = '', and (4) using myfield IS NULL. So I
guess to cover the ground, Microsoft more or less has to have both
expressions in its SQL. The problem is that Word+ODSO do not work
together properly to maintain the overall expression correctly.

What I cannot understand is how you were OK before. It's either because
a. it /was/ OK before, and there has been a change to the way
Word/ODSO works (I've had trouble tracking this in the past because
there have been several fixes in this general area).
b. you were actually using ODBC (or possibly even DDE) to connect
before, and something has changed that is forcing OLE DB instead. With
ODBC, for example the choice you get is still "is blank", but
a. that translates into

Myfield IS NULL

(and not "OR Myfield = '')
b. Word does not go via ODSO for ODBC connections.

In fact, I find it difficult to imagine that Word could have started
changing connection types without you noticing, but that's all I can
think of right now. Another rather remote possibility is that you were
using the old Excel converter to get data from Excel, and that a Word
update has removed it or rendered it useless.

So, is there anything you can do?

Well,
1. if the connection method has changed, you could try to revert to
the one you were using before. You can check
Word-Tools-Options-General-Confirm conversion at open
to ensure that you are asked for the available connection methods after
you have selected the data source in the Select Data Source dialog box.
2. If you are setting up merges where the filter criteria do not need
to change, you can consider modifying the SQL Query string directly in
Word VBA. As long as you do not need to go back into the Query options
dialog, the SQL that you specify should "stick".

However, if there has been a change, it may be worth contacting
Microsoft directly (the people in here are typically volunteers who do
not work for microsoft) and see if they can shed any light on the matter.

Peter Jamieson

http://tips.pjmsn.me.uk

Bill-at-JSC wrote:
I supppose that I am not knowledgeable enough to recognize if this has been
addressed before, so I am asking because this is creating serious problems
for me.

AT some point in the last several months an update to my Office 2003 has
been installed that has resulted in an inability to correctly make changes
in existing merge criteris. Specifically, putting in a change results in
nearly every filtering criteria becoming a two-part OR, with the addition of
an unwanted IF about the file dbeing empty/nulll OR my desired inclusion
criteria. We have lots of empty fields all over lots of Excel databases that
get used in over 100 pre-reports, and all of the records with any of the
fields empty get selected because of this unwanted-but-cannot-be-removed OR
that has begun being added.

I am not a knowledgeable user. I pretty much only know how to maintain and
make minor changes to he existing reports. And I cannot even do that because
some update has changed the way things work such that what I ask for gets
changed to be what I definitely don't want.

How has it come about that Mail Merge is deciding -- incorrectly -- for me
that what I really want is to search for empty cells as well as the cells
containing the values we want? And what can I do to stop it?

If this has been answered already somwhere, please accept my apology for my
ignorance that hasn't recognized it, and explain it more simply so that I'll
get it this time.

Thanx, folks...BillG

OK, based on what you said I'd try doing /one/ connection using
something other than OLEDB and see if you get more of less what you expect.

To do that,
a. check the item I mentioned: Word-Tools-Options-General-Confirm
conversion at open
b. go through the connection process for one of your data sources.
After you've selected the .XLS you should ss a "Confirm data source"
dialog box. It could have options that say
OLE DB Database
(something with "DDE" in it)
(something with "Converter" in it)
(one or more things with "ODBC")

I'd try each of the options other than OLE DB and see what happens.
DDE starts Excel if necessary, and gets it to open the workbook, if
necessary, and will only connect with the first sheet in the book. (If
you recognise that behaviour, that's probably what you were using before).
I'd actually avoid the converter if you can.
With ODBC, you will need to try to ensure that the dialog box that
appears has actually selected the correct .xls file - it the pathnames
are too long you simply cannot see. Then you have to click the Options
button and select all the options in there before you get to see the
worksheet names.

You can get some more info. on some of the acronyms at

http://tips.pjmsn.me.uk/t0003b.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Bill-at-JSC wrote:
Thanx for explanation. I understood most of it (I don't know what is meant
by any of the accronyms like ODSO and ODBC and so forth) but I think I have
an idea of what is happening to me concerning the interpretations of "blank"
and "null" and "empty" cells.

You're correct that the databases being used by any particular report do
change sometimes, and it is biting us when that connection is getting
re-done, because that is when the filter/sort criteria get re-specified,
which is when the additional OR phrase is being added. In fact, when that
happens I've noticed that the choices for filtering have changed (ex. one can
now specify "contains" as well as "equals").

Anyhow, thanx for the response. I'm still between a rock and a hard place
about what to actually do now that I more-or-less understand, but I can now
at least kinda explain to the-powers-that-be.

"Peter Jamieson" wrote:

This has long been a nasty area within Word, but I believe that the
origin of this problem is described in the following article:

http://support.microsoft.com/kb/286880

and that it will occur as soon as you have an OLE DB connection to a
data source and specify "is blank" or perhaps "is not blank" as one of
the criteria in your "Advanced criteria".

NB, the article states that

"
The SQL string for the expression "MyField Is blank" is:
(MyField IS NULL) OR (MyField IS '')
"
which is
a. syntactically incorrect - it should say
(MyField IS NULL) OR (MyField = '')
b. arguable. It all depends on what they mean by "blank". Do they mean
1 has one or more space characters (specifically, ASCII/Unicode
character 32)
2 has one or more "white space characters" (spaces, tabs etc.)
3 has a zero-length string (which in Excel you could insert using
the formula ="")
4 is null (which in Excel appears to correspond to a cell with no
text or formula in it
5 something else?
6 some combination of the above?

The Jet SQL that is used to get data from Excel actually retrieves both
(1) and (3) using Myfield = '', and (4) using myfield IS NULL. So I
guess to cover the ground, Microsoft more or less has to have both
expressions in its SQL. The problem is that Word+ODSO do not work
together properly to maintain the overall expression correctly.

What I cannot understand is how you were OK before. It's either because
a. it /was/ OK before, and there has been a change to the way
Word/ODSO works (I've had trouble tracking this in the past because
there have been several fixes in this general area).
b. you were actually using ODBC (or possibly even DDE) to connect
before, and something has changed that is forcing OLE DB instead. With
ODBC, for example the choice you get is still "is blank", but
a. that translates into

Myfield IS NULL

(and not "OR Myfield = '')
b. Word does not go via ODSO for ODBC connections.

In fact, I find it difficult to imagine that Word could have started
changing connection types without you noticing, but that's all I can
think of right now. Another rather remote possibility is that you were
using the old Excel converter to get data from Excel, and that a Word
update has removed it or rendered it useless.

So, is there anything you can do?

Well,
1. if the connection method has changed, you could try to revert to
the one you were using before. You can check
Word-Tools-Options-General-Confirm conversion at open
to ensure that you are asked for the available connection methods after
you have selected the data source in the Select Data Source dialog box.
2. If you are setting up merges where the filter criteria do not need
to change, you can consider modifying the SQL Query string directly in
Word VBA. As long as you do not need to go back into the Query options
dialog, the SQL that you specify should "stick".

However, if there has been a change, it may be worth contacting
Microsoft directly (the people in here are typically volunteers who do
not work for microsoft) and see if they can shed any light on the matter.

Peter Jamieson

http://tips.pjmsn.me.uk

Bill-at-JSC wrote:
I supppose that I am not knowledgeable enough to recognize if this has been
addressed before, so I am asking because this is creating serious problems
for me.

AT some point in the last several months an update to my Office 2003 has
been installed that has resulted in an inability to correctly make changes
in existing merge criteris. Specifically, putting in a change results in
nearly every filtering criteria becoming a two-part OR, with the addition of
an unwanted IF about the file dbeing empty/nulll OR my desired inclusion
criteria. We have lots of empty fields all over lots of Excel databases that
get used in over 100 pre-reports, and all of the records with any of the
fields empty get selected because of this unwanted-but-cannot-be-removed OR
that has begun being added.

I am not a knowledgeable user. I pretty much only know how to maintain and
make minor changes to he existing reports. And I cannot even do that because
some update has changed the way things work such that what I ask for gets
changed to be what I definitely don't want.

How has it come about that Mail Merge is deciding -- incorrectly -- for me
that what I really want is to search for empty cells as well as the cells
containing the values we want? And what can I do to stop it?

If this has been answered already somwhere, please accept my apology for my
ignorance that hasn't recognized it, and explain it more simply so that I'll
get it this time.

Thanx, folks...BillG

Wow, lots of cool stuff to know & try. I won't have time to do any
experimenting until after our Monday morning mtg (it is Friday afternoon
here) but at that time I surely will create backup copies and make attempts.
Thanx again, have a nice weeked...BG

"Peter Jamieson" wrote:

OK, based on what you said I'd try doing /one/ connection using
something other than OLEDB and see if you get more of less what you expect.

To do that,
a. check the item I mentioned: Word-Tools-Options-General-Confirm
conversion at open
b. go through the connection process for one of your data sources.
After you've selected the .XLS you should ss a "Confirm data source"
dialog box. It could have options that say
OLE DB Database
(something with "DDE" in it)
(something with "Converter" in it)
(one or more things with "ODBC")

I'd try each of the options other than OLE DB and see what happens.
DDE starts Excel if necessary, and gets it to open the workbook, if
necessary, and will only connect with the first sheet in the book. (If
you recognise that behaviour, that's probably what you were using before).
I'd actually avoid the converter if you can.
With ODBC, you will need to try to ensure that the dialog box that
appears has actually selected the correct .xls file - it the pathnames
are too long you simply cannot see. Then you have to click the Options
button and select all the options in there before you get to see the
worksheet names.

You can get some more info. on some of the acronyms at

http://tips.pjmsn.me.uk/t0003b.htm

Peter Jamieson

http://tips.pjmsn.me.uk

Bill-at-JSC wrote:
Thanx for explanation. I understood most of it (I don't know what is meant
by any of the accronyms like ODSO and ODBC and so forth) but I think I have
an idea of what is happening to me concerning the interpretations of "blank"
and "null" and "empty" cells.

You're correct that the databases being used by any particular report do
change sometimes, and it is biting us when that connection is getting
re-done, because that is when the filter/sort criteria get re-specified,
which is when the additional OR phrase is being added. In fact, when that
happens I've noticed that the choices for filtering have changed (ex. one can
now specify "contains" as well as "equals").

Anyhow, thanx for the response. I'm still between a rock and a hard place
about what to actually do now that I more-or-less understand, but I can now
at least kinda explain to the-powers-that-be.

"Peter Jamieson" wrote:

This has long been a nasty area within Word, but I believe that the
origin of this problem is described in the following article:

http://support.microsoft.com/kb/286880

and that it will occur as soon as you have an OLE DB connection to a
data source and specify "is blank" or perhaps "is not blank" as one of
the criteria in your "Advanced criteria".

NB, the article states that

"
The SQL string for the expression "MyField Is blank" is:
(MyField IS NULL) OR (MyField IS '')
"
which is
a. syntactically incorrect - it should say
(MyField IS NULL) OR (MyField = '')
b. arguable. It all depends on what they mean by "blank". Do they mean
1 has one or more space characters (specifically, ASCII/Unicode
character 32)
2 has one or more "white space characters" (spaces, tabs etc.)
3 has a zero-length string (which in Excel you could insert using
the formula ="")
4 is null (which in Excel appears to correspond to a cell with no
text or formula in it
5 something else?
6 some combination of the above?

The Jet SQL that is used to get data from Excel actually retrieves both
(1) and (3) using Myfield = '', and (4) using myfield IS NULL. So I
guess to cover the ground, Microsoft more or less has to have both
expressions in its SQL. The problem is that Word+ODSO do not work
together properly to maintain the overall expression correctly.

What I cannot understand is how you were OK before. It's either because
a. it /was/ OK before, and there has been a change to the way
Word/ODSO works (I've had trouble tracking this in the past because
there have been several fixes in this general area).
b. you were actually using ODBC (or possibly even DDE) to connect
before, and something has changed that is forcing OLE DB instead. With
ODBC, for example the choice you get is still "is blank", but
a. that translates into

Myfield IS NULL

(and not "OR Myfield = '')
b. Word does not go via ODSO for ODBC connections.

In fact, I find it difficult to imagine that Word could have started
changing connection types without you noticing, but that's all I can
think of right now. Another rather remote possibility is that you were
using the old Excel converter to get data from Excel, and that a Word
update has removed it or rendered it useless.

So, is there anything you can do?

Well,
1. if the connection method has changed, you could try to revert to
the one you were using before. You can check
Word-Tools-Options-General-Confirm conversion at open
to ensure that you are asked for the available connection methods after
you have selected the data source in the Select Data Source dialog box.
2. If you are setting up merges where the filter criteria do not need
to change, you can consider modifying the SQL Query string directly in
Word VBA. As long as you do not need to go back into the Query options
dialog, the SQL that you specify should "stick".

However, if there has been a change, it may be worth contacting
Microsoft directly (the people in here are typically volunteers who do
not work for microsoft) and see if they can shed any light on the matter.

Peter Jamieson

http://tips.pjmsn.me.uk

Bill-at-JSC wrote:
I supppose that I am not knowledgeable enough to recognize if this has
been addressed before, so I am asking because this is creating serious
problems for me.

At some point in the last several months an update to my Office 2003 has
been installed that has resulted in an inability to correctly make changes
in existing merge criterias. Specifically, putting in a change results in
nearly every filtering criteria becoming a two-part OR, with the addition of
an unwanted IF about the cell being empty/null OR my desired inclusion
criteria. We have lots of empty fields all over lots of Excel databases that
get used in over 100 pre-defined reports, and all of the records with any
of the subject fields empty get selected because of this
unwanted-but-cannot-be-removed "OR" that has begun being added.

I am not a knowledgeable user. I pretty much only know how to maintain
and make minor changes to the existing reports. And I cannot even do
that because some update has changed the way things work such that
what I ask for gets changed to be what I definitely don't want.

How has it come about that Mail Merge is deciding -- incorrectly -- for me
that what I really want is to search for empty cells as well as the cells
containing the values we want? And what can I do to stop it?

If this has been answered already somwhere, please accept my apology for
my ignorance that hasn't recognized it, and explain it more simply so that
I'll get it this time.

Thanx, folks...BillG