Re: mail merge filter criteria

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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
.

Relevant Pages

  • Re: mail merge filter criteria
    ... go through the connection process for one of your data sources. ... the criteria in your "Advanced criteria". ... has a zero-length string (which in Excel you could insert using ... you have selected the data source in the Select Data Source dialog box. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Filtering receipients automatically changing
    ... Word adds criteria as you describe if you connect to e.g. Excel using the defualt methofd. ... if you change to DDE, that problem will disappear, but in 2007 you may find the connection is not as reliable, you don't get Unicode characters across, you can only connect to the first sheet in an Excel workbook, and you may have to pay attention to how you define your filter conditions. ... After you have selected the data source, you should see a "Confirm Data Source dialog box. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Mail Merging a Selection from within an Excel Sheet
    ... sequence criteria. ... macro, things are slightly different. ... number of reasons - what, for example, if you sort the data source in Excel. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Automatic filters in word mail merge
    ... you specify the start and finish data source record numbers when you initiate the merge ... When the user specifies criteria using the dropdowns or Query options, from a programming perspective you don't get to see what the user specified in those dropdowns. ... The SQL generated depends partly on the type of data source - e.g., when the data source is a Word document, Word uses a very smple built-in dialect of SQL. ... If your users could choose any old data source, things could be pretty hard, and perhaps in that case it might be better to present your own selection criteria forms and generate your own queries, etc. etc. ...
    (microsoft.public.word.docmanagement)
  • Re: Automatic filters in word mail merge
    ... based on identification criteria of a person--ie. ... which would set up the filter in word. ... able to open the template, add the data source, then preview and print ... SQL code that Word generated as a consequence of the user's choices. ...
    (microsoft.public.word.docmanagement)