I have noticed that the filter option for Word on Mail Merges does unexpected things and seems to ‘give up’ in somewhat complex filters. Here’s an example from an actual situation and my attempt at analyzing/framing the issue:
–>
when you ‘chain’ a bunch of filter conditions, each element is compared to any other element that uses the ‘OR’ operator. You might think you are restricting the query results with each step you add but it seems this is not the case. For example, you restrict the records result with this: Filter where CRCGType = CRCGA OR CRCGType = CRCGF [no CRCGs are returned]. But if you add AND MemberType = Coordinator it returns CRCGF, CRCGA, Chair and Coordinator rather than just Coordinators. Not what you’d expect, huh? If you put in OR MemberType = Coordinator it returns all the records (including CRCGs!) I’ve noticed that Microsoft tends to ‘give up’ on somewhat complex queries and defaults to everything.
Lesson: avoid using OR + AND. In the case of your query for CRCGA’s and F’s for Coords and Chairs, this works best:
CRCG Type Not Equal to CRCG AND Member Type Not Equal to Co-Chair (use two conditions with AND) — this returns just CRCGA’s and F’s and (if they exist) Others in the query set. Not perfect, but it’s real close.
<–
Question #1: Is there a general recognized problem with using somewhat complex filtering criteria such that, when you apply a set of conditions intending to pinpoint a set of record matching more than, say, 2 filter criteria the mail merge ignores the settings and just dumps all the data? This seems to happen often enough that I advise people not to get too fancy with the filtering methods.
Question #2: What exactly is the logic of AND and OR switches in the filter string? For example, as I noted above, using
Filter when Criteria1= [Criteria1_Criteria] AND Criteria2 = [Criteria2_Criteria] then you get the desired set, i.e. all CriteriaX are not displayed. However, in this setting:
Filter when Criteria1= [Criteria1_Criteria] AND Criteria2 = [Criteria2_Criteria] OR Criteria3 = [Criteria3_Criteria], I keep getting everything.
It's just like Word looked at this and choked and gave all the data — or, the use of OR meant that any record is compared to third one regardless of the first two settings? The latter possiblity doesn't seem to be happening actually, but I didn't check the results extensively. In any case throwing AND + OR around doesn't seem to work too well. Any hints on what is happening here? I have grown to distrust a MS-supplied method but that's not all that suprising — just curious as to what is really happening here.
BTW, I am doing this merge from an Access table and can, of course, set up specific queries to supply what I am trying to filter in Word, so I am not up a creek really. However, I am encouraging people to do Mail Merge from Outlook to Word merge files as that is incredibly easy to set up. For now, they know to look for and delete spurious records.
Thanks for any insight ya'll might provide!
