Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Word 2007 mail merge not filtering correctly

Posted on 2011-09-29
7
Medium Priority
?
339 Views
Last Modified: 2012-05-12
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
Comment
Question by:MaxwellTurner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36817415
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36817536
<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
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 36817794
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36817926
Then post the SQL for the query please
0
 
LVL 1

Accepted Solution

by:
MaxwellTurner earned 0 total points
ID: 36818210
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36824409
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
 
LVL 1

Author Closing Comment

by:MaxwellTurner
ID: 36938206
Kinda figured it out on my own ...
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question