Filtering

[Brenda from Michigan]

What is the proper method to filter in a merge using Word 2002 or 2003 and
Excel as the data file? When we click Select Recipients | a down arrow and
Advanced, select a field and enter our query (Greater than and a dollar
figure), we get an error that the data records are empty or no data matched
the query when in fact dozens of records match the query. Thanks in Advance!

 

[Peter Jamieson]

You are probably doing the right thing, but when you enter your
criteria, Word generates a SQL statement that does not always do the
right thing.

Can you provide more detail about what crteria you are trying to set up?

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Brenda from Michigan]

We want to merge only the records in the field that have a dollar value
greater than $21.00. I have tried merges that include that field and merges
that do not use that particular in the Main Document. Strangely, sometimes a
few records briefly pop up in the Select Recipients screen (too few to be
accurate) and other times we get get the error that nothing matches.

What else can I tell you about our problem? We are merging using DDE.
Thanks for your quick response. I've spent about 2 hours on this so far
this morning.

- Brenda

 

[Peter Jamieson]

If you have formatted the amount with a $ sign in Excel, then I do not
think any comparison you can set up in Query Options will work - Word
can get confused about whether you are comparing text or a number, but
AFAICS it tries to do a text comparison, which means that for example

amount greater than $21

will exclude anything that starts with $1, e.g. $100

It may be better if you can do one of the following:
a. use OLE DB (the default) to get your data - but presumably you are
using DDE because of the other problems you can have with OLE DB
b. change the formatting of the amount column so it does not include a
$ sign, then insert the $ sign using a Word numeric field switch
c. use a SKIPIF field to do the test, e.g.

{ SKIPIF { MERGEFIELD myamount \#0.00 } <= 21 }



(That won't help if you are for example merging to labels or doing a
complex merge).

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Brenda from Michigan]

I have tried changing the format of the Excel column to Currency, to General
and to Accounting. I've had both the $ sign showing and no $ sign. I've
tried actually merging the data from this field into the Main Document, and
not merging but just using that field to filter the records wanted.

I've also inserted the Excel document into a new document in case it was
corrupted. I've created a new Main Document with just a few fields in case
IT was corrupted.

I've tried merging Ole, DDE, Excel converted, and ODBC.

I don't know what else to do.

 

[Peter Jamieson]

Let's see if we can see what query code Word is generating.

Let's suppse the amount column is called amount.

Can you
a. start with a completely new Word document (just in case...)
b. connect using OLE DB (the default)
c. ensure that { EMRGEFIELD amount } just shows a number, e.g. 12.34
d. use the advanced query options to enter the criterion as

myfield greater than 21

e. see what that does. If it works, try again in your original
document. If that doesn't work, we have to assume there is something
wrong with that document.
f. if that does not work, can you go into Tools->Macro and start the
Visual Basic editor, then type the following into the Immediate Window
(if you do not see it, press ctrl-G

? activedocument.mailmerge.datasource.querystring

Press enter, then copy the result into a message in this conversation.
it may tell us something.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Brenda from Michigan]

Let's see if we can see what query code Word is generating.

Let's suppse the amount column is called amount.

Can you
a. start with a completely new Word document (just in case...)
b. connect using OLE DB (the default)
c. ensure that { EMRGEFIELD amount } just shows a number, e.g. 12.34
d. use the advanced query options to enter the criterion as

myfield greater than 21

e. see what that does. If it works, try again in your original
document. If that doesn't work, we have to assume there is something
wrong with that document.
f. if that does not work, can you go into Tools->Macro and start the
Visual Basic editor, then type the following into the Immediate Window
(if you do not see it, press ctrl-G

? activedocument.mailmerge.datasource.querystring

Press enter, then copy the result into a message in this conversation.
it may tell us something.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Brenda from Michigan]

I'll start with this tomorrow and let you know what happens.

- Brenda

 

[Brenda from Michigan]

I finally got it figured out. When we filter by "Greater than" and an
amount, it won't work at all. When I filtered by "Greater than or equal to"
and added one penny to the amount wanted, it works fine. In this case the
Excel file field was set to Accounting and did include the $ sign, which does
carry over when using DDE. I'm not sure why the Greater than didn't work,
but we did find a workaround.

Thanks for your suggestions!
Brenda