Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Indiana, Indiana, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail Merge Advanced Filter Problem (2003)

    Edited by HansV to provide link to MSKB article - see <!help=19>Help 19<!/help>

    Word is adding an extra criteria into my advanced filter specification when I try to filter on a blank field in an Access database. I have two fields called Acute (yes/no or -1,0 values) and Active (YES or blank) in the database. My filter is "If Acute = -1 AND Active is blank". After clicking OK, Word adds an additional criteria "Or Active is blank" which does not give me the correct subset. (Note: this also happens when using Excel as the data source.)

    Microsoft KB Filters for blanks and nonblanks are replicated in the filter criteria list in Word lists the cause as the SQL string being parsed to (Active IS NULL) OR (Active IS '') which are both passed to the Office DataSource Object (ODSO), which produces two "is blank" entries in the query criteria list. They list a way to clear the values, but not a way to actually filter on the blank field. A partial work around found in TechRepublic is to filter using "contains" conditions. So if I construct the filter as "If Acute = -1 AND Active Does Not Contain YES", the filter works. But, the next time you reopen the advance filter dialog box, it is empty. But the filter is still being applied. You cannot delete the filter unless you enter in a different filter and then clear the dialog box.

    While this work around works with only one value for the field, if multiple values are vaild for a field along with the blank option, to filter on the blank option you will need to use the "does not contain" criteria for each valid entry.

    My question is: is there a better work around (other than not filtering on a blank field)? Microsoft has acknowledged that this is a "problem" in 2007, but does not seem to have fixed it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail Merge Advanced Filter Problem (2003)

    If you know how SQL works, you could try setting the SQL string directly:
    - Activate the Visual Basic Editor (Alt+F11).
    - Activate the Immediate window (Ctrl+G).
    - Type the following line, then press Enter:

    ? ActiveDocument.MailMerge.DataSource.QueryString

    You'll see something like
    <code>
    SELECT * FROM `tblSomething` WHERE `Acute` = -1 And (`Active` IS NULL Or `Active` = '')
    </code>
    You can edit this string, for example to
    <code>
    SELECT * FROM `tblSomething` WHERE `Acute` = -1 And `Active` IS NULL
    </code>
    and then make an instruction
    <code>
    ActiveDocument.MailMerge.DataSource.QueryString = "SELECT * FROM `tblSomething` WHERE `Acute` = -1 And `Active` IS NULL"
    </code>
    Click in this line and press Enter. Because you set the SQL string directly, it will not be modified by the ODSO, as long as you avoid setting the filter the normal way in this document from now on.

  3. #3
    New Lounger
    Join Date
    Apr 2001
    Location
    Indiana, Indiana, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail Merge Advanced Filter Problem (2003)

    Worked like a charm. I will keep this work around handy for other mail merges.
    Thank you,
    Jim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •