Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
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! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
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 wrote: 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! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
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" wrote: 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 wrote: 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! |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
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 wrote: 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" wrote: 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 wrote: 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! |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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! |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
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 wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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! |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
"Peter Jamieson" wrote: 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 wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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! |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
I'll start with this tomorrow and let you know what happens.
- Brenda "Peter Jamieson" wrote: 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 wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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! |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Filtering
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 "Peter Jamieson" wrote: 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 wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing recipients by filtering | Mailmerge | |||
Filtering from Access Database | Mailmerge | |||
filtering a column | Tables | |||
Filtering Data | Mailmerge | |||
Filtering Data | Mailmerge |