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