A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Mail Merge with database and filtering



 
 
Thread Tools Display Modes
  #1  
Old December 10th 09, 06:37 PM posted to microsoft.public.word.mailmerge.fields
osbornauto
external usenet poster
 
Posts: 3
Default Mail Merge with database and filtering

Working with Office '07, I have a Word document (postcard) that pulls mail
merge info from an '07 Access database table. While in Word, under the
"Mailings" tab, I click on "edit recipient list" and want to select only
persons that have a blank email address and have spent over $1,000. I create
the filter as "emailaddress" is blank AND "payamt" is greater than or equal
to 1000. Then click OK. When I get back to the document, it should only
have 4 recipients, however there are 34. It has changed the query to email
is blank and payamt is over 1000 OR email is blank. So, the query has added
another layer with the "Or email is blank". I try to clear trhe query and
start over, but same results. Any ideas?
Ads
  #2  
Old December 10th 09, 09:31 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,583
Default Mail Merge with database and filtering

I believe this te problem described in an old article:

http://support.microsoft.com/kb/286880

so it does not look as if a fix will be available any time soon, if ever.

The only way I know how to deal with this reasonably easily from within
Word is to set up the query using VBA. If you open your mail merge main
document, then go into the VBA Editor (VBE), open the Immediate window
(e.g. use VBE Tools-View if you do not see it), then type

?activedocument.mailmerge.datasource.querystring

You should see the current query string. In your case, Word probably
tries to use something like

SELECT * FROM `Tablename` WHERE (`emailaddress` IS NULL OR
`emailaddress` = '') AND `payamt` = 1000

In other words, Word tries to deal with the two main possible ways you
could have a "blank" emailaddress.

Unfortunately, ODSO (see the article) screws this up and turns it into

SELECT * FROM `Tablename` WHERE (`emailaddress` IS NULL OR
`emailaddress` = '') OR (`emailaddress` IS NULL OR `emailaddress` = '')
AND `payamt` = 1000

which introduces the problem you see. As far as I can see, the only way
to fix this is to omit the test for NULL, using e.g.

SELECT * FROM `Tablename` WHERE `emailaddress` = '' AND `payamt` = 1000

/and/ to avoid going back into the Edit Recipients dialog box. If you go
into it, the problem will recur.

To set the querystring, you need to issue the following command in the
immediate window:

activedocument.mailmerge.datasource.querystring = "SELECT * FROM
`Tablename` WHERE `emailaddress` = '' AND `payamt` = 1000"

and press enter. Or you may find it useful to put small subroutines in
your normal.dotm file, e.g.

Sub getquery()
debug.print activedocument.mailmerge.datasource.querystring
end sub

Sub setquery()
activedocument.mailmerge.datasource.querystring = "whatever query you
want"
end sub

etc.

I do not think Microsoft has any excuse for not fixing this type of
error, as what they are currently doing is not "support"

Peter Jamieson

http://tips.pjmsn.me.uk

On 10/12/2009 17:37, osbornauto wrote:
Working with Office '07, I have a Word document (postcard) that pulls mail
merge info from an '07 Access database table. While in Word, under the
"Mailings" tab, I click on "edit recipient list" and want to select only
persons that have a blank email address and have spent over $1,000. I create
the filter as "emailaddress" is blank AND "payamt" is greater than or equal
to 1000. Then click OK. When I get back to the document, it should only
have 4 recipients, however there are 34. It has changed the query to email
is blank and payamt is over 1000 OR email is blank. So, the query has added
another layer with the "Or email is blank". I try to clear trhe query and
start over, but same results. Any ideas?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail merge and Excel filtering BeetleB Mailmerge 4 November 29th 07 10:05 AM
VB code for filtering mail merge Bev Mailmerge 4 June 13th 06 10:50 PM
Filtering from Access Database novice Mailmerge 1 June 10th 06 09:27 AM
Filtering in Mail Merge Labels Word 2000 DannyJ Mailmerge 1 May 19th 05 07:46 AM
Mail Merge - Filtering data Louise Mailmerge 0 January 31st 05 03:41 PM


All times are GMT +1. The time now is 11:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.