Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Word 2007 mail merge not filtering correctly

I have several mail merge documents (Word 2007) that use an Access 2007 query as the datasource.  The Access query is filtered with criteria entered for some columns ... however, when I open the Word mail merge document, it retrieves ALL records and seems to ignore the criteria filters I have in the query ??!!  I am aware I can filter within the Word document itself, but I prefer not to do that to keep the merges user-friendly for the users.

Does anyone know why the filtering criteria works at the query level, but not in the mail merge documents?

Max
0
MaxwellTurner
Asked:
MaxwellTurner
  • 4
  • 3
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
Sounds odd.

Make *Sure* the mail merge source is indeed to query you want. (Not a table, or some other query)

Then run the query by itself and verify that it is filtering correctly
0
 
Jeffrey CoachmanMIS LiasonCommented:
<The Access query is filtered with criteria entered for some columns>
How exactly?
In the SQL itself, of by some selections that the user makes...?

In other words, make sure the Filters are being applied.
If you are selecting the criteria from a form, make sure the form is open and the selections are made, and the filters are being applied.
0
 
MaxwellTurnerAuthor Commented:
This is odd indeed ... triple confirmed the document was linked to the corrrect query ... all criteria is within the query (no forms).  To make it stranger, I have criteria in several columns, but just ONE column is not filtering properly at the merge document level.  Must be a syntax issue that Access and Word are not completely compatible ... ??  I've certainly encountered much of this with Access vs. SQL Server syntax.

Max
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jeffrey CoachmanMIS LiasonCommented:
Then post the SQL for the query please
0
 
MaxwellTurnerAuthor Commented:
It is a basic SELECT-FROM-WHERE query.  The only part of the WHERE claus that is ignored by Word is:

Not Like "*5l*" And Not Like "*6l*" And Not Like "1*" And Not Like "*lj 1*" And Not Like "*lj1*"

... and upon closer inspection, it is the only column that is filtered using LIKE.  I found this on google, which confirms there is an issue using the like condition in a query that is used in a mail merge.

http://ittidbit.blogspot.com/2011/08/ms-word-mail-merge-using-ms-access-like.html

I don't like the DNS solution (unless I can use DSN-less connection with Word?)  This Dbase is maintained each night, so I think my best option is to create an additional 'flag' column in my table, and update it to YES or NO based on the above like condition, then use the flag column to filter the merge query.

Max
0
 
Jeffrey CoachmanMIS LiasonCommented:
Yeah, wild cards use a lot of processing because they must check all the possibilities.

Also try using Instr()

something like this perhaps:
    Instr([YourField],"5l")=0

This translates into: [YourField] does not contain "5l"

...but your "Flag" column sounds like a good option as well.

;-)

Jeff
0
 
MaxwellTurnerAuthor Commented:
Kinda figured it out on my own ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now