MailMerge keeps first selected record even if does not match query

[C2Ranches]

I am trying to do a mail merge with the task pane or the mail merge tool bar.
Sample Data from excel spreadsheet:
CaseNumber Name1 Name2
05146 Alan Wyoming
05150 Rita Wyoming
06297 Irv New York
06298 Justin Colorado
06299 Brian Arizona
06300 Greg Wyoming
06301 Sara New York
06302 Bonnie Colorado
03101 Frank Colorado
03102 Helda Arizona
03103 Tori Wyoming

Setup Document to do catalog, Query is if CaseNumber is equal to or greater
than "06". When merging it all ways includes the first record in the list
even though it is 05. Happens when using task pane merge or tool bar "Mail
Merge Helper". Happens even when that record is unselected in the Mal
Recipients window. It seems as though the task pane help has selected the
first record on the list and will not let it go. I have tried inserting a
blank row, resorting the data in excel, opening a different database and
running a query, and then going back but it all ways selects the first record
in the database and adds that into the query. In this case the first time the
query was run 05146 – Alan – Wyoming was the first record. That record shows
in all queries now. Any ideas. You can repeat the issue with this data. I
have a user that this is happening to, and I was able to repeat it on my
machine with this made up data. Any ideas?

 

[Peter Jamieson]

I tried the same thing here and did not see this particular problem, but
a. have you tried the different connection methods (check Word
Tools|Options|General|"Confirm conversions at open", reconnect to the source
and try the DDE option when it is offered.
b. Can you go into the Immediate pane of the VB editor, execute the
following lines, and report back?

Print ActiveDocument.MailMerge.DataSource.QueryString
Print ActiveDocument.MailMerge.DataSource.TableName

here, I see

SELECT * FROM `Sheet1$` WHERE `Casenumber` > '06'

if the connection type is OLEDB, and a few extra brackets if it is ODBC. (I
doubt if there will be anything wrong there but it's worth a look).

Peter Jamieson

 

[C2Ranches]

I should have mention that this is only happening in Word 2003 using excel
2003 as the database. It does work perfect in Office 2000
Thanks

 

[Peter Jamieson]

Then definitely try the DDE connection method I mentioned in my previous
message:
check Word Tools|Options|General|"Confirm conversions at open", reconnect to
the source
and try the DDE option when it is offered.

Peter Jamieson

 

[C2Ranches]

Okay tired it like you suggested and the query does not work at all. It pulls
in all of the data not just the 06 or greater

 

[C2Ranches]

Well it seems to be working now, so thanks for your help it was the DDE. You
are the best!

 

[Peter Jamieson]

Curious. I was just having a further look on both Word 200 and Word 2003. On
both, I found I had to add single quotes aroung the 06 when specifying the
filter condition with a DDE connection.

Trouble is that you really want the comparision to be a text comparison, and
that depends on whether Word recognises that the column in question is a
text column or a numeric column (and that's a long story). I tried
formatting as text and using a custom format of 00000, but in either case
when you connect using OLEDB I would expect Word to decide that the column
is actually numeric, at which point specifying a condition like Greater than
06 won't work how you intend it to anyway (it will just select every row
with a Casenumber greater than number 6, which will be everything except
numbers 00001, 00002, 00003, 00004, 00005, 00006). Perhaps that was what was
actually wrong before, but I had the impression that the only "05" record
retained was the first one.

Anyway, it sounds as if the problem is solved, for now!

Peter Jamieson

 

[C2Ranches]

My 06 issue was exactly that. I need to do 06000 - the three 000 are the case
number, the 06 is the year. But the issue of it all ways no matter what the
query stated putting the 05 ID first was solved with DDE. It also fixed some
other issues that the user was having with the database and querys. So does
2003 interpret excel different? or did 2000 always ask you what format you
want and this is now an option in 2003?

Anyway thats so much for your help Peter!

 

[Peter Jamieson]

So does

2003 interpret excel different? or did 2000 always ask you what format you
want and this is now an option in 2003?

2000 lets you get data from Excel using
a. DDE
b. a text converter
c. ODBC

Each has different characteristics, e.g. to use DDE you had to have a copy
of Excel on the same system, the converter always asks questions when you
use it, ODBC tends to get the "underlying" data, and so on.

The default was DDE.

2003 lets you get data from Excel using
a. DDE
b. ODBC
c. OLEDB

The default is OLEDB.

Each has different characteristics, even though ODBC and OLEDB are based on
the same mechanism for getting data from Excel. The converter is no longer
provided although if you had it on Word 2000 and upgraded it may still be on
your system and it can still be had if you really need it.

Peter Jamieson