Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge Labels
Using MSOffice 2003 SP(2) I have the following Excel fields on a label page
and it prints out all the labels just fine. LastName Initial Address1 Address2 City, Prov PostalCode Country I would like to be able to use the same Excel file and using I guess IF commands print the labels IF the country = Canada, print the labels IF the country Canada, print the labels IF the country = Canada and the email field is blank, print the labels IF the country Canada and the email field is blank, print the labels Any assistance will be greatly appreciated. -- Regards Michael Koerner |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge Labels
From your examples it's not completely clear what you're trying to do -
Eliminate labels that are not for Canada? Or avoid printing the Country field if it's Canada? Or some combination of the two. Assuming you want to eliminate records, you really need to filter your records before you do the merge rather than use IF fields to try to eliminate them. If you're a regular Excel user, you may know how to do that easily in Excel and if so, that's probably the best approach. In Word... Open your mail merge main document. Enable the Mail Merge Toolbar (Tools|Customize). Then click the 3rd button (Mail Merge Recipients). Click the tiny dropdown at the top of the "Country" column. If you see "Canada" listed, select it. if not, click Advanced..." and use the form in the Query Options Dialog box to select Country Equals Canada and Email is not blank or whatever the appropriate criteria are. Word uses these criteria to construct a SQL Query but unfortunately it does not always get it right. If your criteria are more complicated than the Query Options dialog can manage you have to use VBA to issue the correct SQL query (which is one reason why it's better to filter in Excel if you can). Peter Jamieson "Michael Koerner" wrote in message ... Using MSOffice 2003 SP(2) I have the following Excel fields on a label page and it prints out all the labels just fine. LastName Initial Address1 Address2 City, Prov PostalCode Country I would like to be able to use the same Excel file and using I guess IF commands print the labels IF the country = Canada, print the labels IF the country Canada, print the labels IF the country = Canada and the email field is blank, print the labels IF the country Canada and the email field is blank, print the labels Any assistance will be greatly appreciated. -- Regards Michael Koerner |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge Labels
Using the Filter in Excel seems to be the best approach. I'm kinda doing
that right now by having 4 different Excel sheets (LOL) I'll ask in the Excel group, thanks very much. -- Regards Michael Koerner "Peter Jamieson" wrote in message ... From your examples it's not completely clear what you're trying to do - Eliminate labels that are not for Canada? Or avoid printing the Country field if it's Canada? Or some combination of the two. Assuming you want to eliminate records, you really need to filter your records before you do the merge rather than use IF fields to try to eliminate them. If you're a regular Excel user, you may know how to do that easily in Excel and if so, that's probably the best approach. In Word... Open your mail merge main document. Enable the Mail Merge Toolbar (Tools|Customize). Then click the 3rd button (Mail Merge Recipients). Click the tiny dropdown at the top of the "Country" column. If you see "Canada" listed, select it. if not, click Advanced..." and use the form in the Query Options Dialog box to select Country Equals Canada and Email is not blank or whatever the appropriate criteria are. Word uses these criteria to construct a SQL Query but unfortunately it does not always get it right. If your criteria are more complicated than the Query Options dialog can manage you have to use VBA to issue the correct SQL query (which is one reason why it's better to filter in Excel if you can). Peter Jamieson "Michael Koerner" wrote in message ... Using MSOffice 2003 SP(2) I have the following Excel fields on a label page and it prints out all the labels just fine. LastName Initial Address1 Address2 City, Prov PostalCode Country I would like to be able to use the same Excel file and using I guess IF commands print the labels IF the country = Canada, print the labels IF the country Canada, print the labels IF the country = Canada and the email field is blank, print the labels IF the country Canada and the email field is blank, print the labels Any assistance will be greatly appreciated. -- Regards Michael Koerner |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge Labels
While I agree with Peter about filtering in the data file, a possible
alternative method would be to have four different merge documents each accessing the same data file. In this case the label would have Case 1 {IF {Mergefield Country} = "Canada" "{Mergefield LastName} {Mergefield Initial} {Mergefield Address1} {Mergefield Address2} {Mergefield City}, {Mergefield Prov} {Mergefield PostalCode} {Mergefield Country}"} Case 2 {IF {Mergefield Country} = "Canada" "{Mergefield LastName} {Mergefield Initial} {Mergefield Address1} {Mergefield Address2} {Mergefield City}, {Mergefield Prov} {Mergefield PostalCode} {Mergefield Country}"} Case 3 { IF { Mergefield Country } = "Canada" "{ IF { Mergefield Email } = "" "{ Mergefield LastName } { Mergefield Initial } { Mergefield Address1 } { Mergefield Address2 } { Mergefield City }, { Mergefield Prov } { Mergefield PostalCode } { Mergefield Country }" ""}" "" } Case 4 { IF { Mergefield Country } "Canada" "{ IF { Mergefield Email } = "" "{ Mergefield LastName } { Mergefield Initial } { Mergefield Address1 } { Mergefield Address2 } { Mergefield City }, { Mergefield Prov } { Mergefield PostalCode } { Mergefield Country }" ""}" "" } This would test each record to see that it fits the criteria You could even save each of the four blocks of fields as autotext entries and add them to the first label of a blank merge label document and propagate then merge. Note that the fields are entered from the keyboard using CTRL+F9 for each pair of brackets. For other examples see http://www.gmayor.com/formatting_word_fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Michael Koerner wrote: Using the Filter in Excel seems to be the best approach. I'm kinda doing that right now by having 4 different Excel sheets (LOL) I'll ask in the Excel group, thanks very much. "Peter Jamieson" wrote in message ... From your examples it's not completely clear what you're trying to do - Eliminate labels that are not for Canada? Or avoid printing the Country field if it's Canada? Or some combination of the two. Assuming you want to eliminate records, you really need to filter your records before you do the merge rather than use IF fields to try to eliminate them. If you're a regular Excel user, you may know how to do that easily in Excel and if so, that's probably the best approach. In Word... Open your mail merge main document. Enable the Mail Merge Toolbar (Tools|Customize). Then click the 3rd button (Mail Merge Recipients). Click the tiny dropdown at the top of the "Country" column. If you see "Canada" listed, select it. if not, click Advanced..." and use the form in the Query Options Dialog box to select Country Equals Canada and Email is not blank or whatever the appropriate criteria are. Word uses these criteria to construct a SQL Query but unfortunately it does not always get it right. If your criteria are more complicated than the Query Options dialog can manage you have to use VBA to issue the correct SQL query (which is one reason why it's better to filter in Excel if you can). Peter Jamieson "Michael Koerner" wrote in message ... Using MSOffice 2003 SP(2) I have the following Excel fields on a label page and it prints out all the labels just fine. LastName Initial Address1 Address2 City, Prov PostalCode Country I would like to be able to use the same Excel file and using I guess IF commands print the labels IF the country = Canada, print the labels IF the country Canada, print the labels IF the country = Canada and the email field is blank, print the labels IF the country Canada and the email field is blank, print the labels Any assistance will be greatly appreciated. -- Regards Michael Koerner |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge Labels
On reflection - perhaps not. This would leave blank labels where the data
does not match. Stick to the filtering -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: While I agree with Peter about filtering in the data file, a possible alternative method would be to have four different merge documents each accessing the same data file. In this case the label would have Case 1 {IF {Mergefield Country} = "Canada" "{Mergefield LastName} {Mergefield Initial} {Mergefield Address1} {Mergefield Address2} {Mergefield City}, {Mergefield Prov} {Mergefield PostalCode} {Mergefield Country}"} Case 2 {IF {Mergefield Country} = "Canada" "{Mergefield LastName} {Mergefield Initial} {Mergefield Address1} {Mergefield Address2} {Mergefield City}, {Mergefield Prov} {Mergefield PostalCode} {Mergefield Country}"} Case 3 { IF { Mergefield Country } = "Canada" "{ IF { Mergefield Email } = "" "{ Mergefield LastName } { Mergefield Initial } { Mergefield Address1 } { Mergefield Address2 } { Mergefield City }, { Mergefield Prov } { Mergefield PostalCode } { Mergefield Country }" ""}" "" } Case 4 { IF { Mergefield Country } "Canada" "{ IF { Mergefield Email } = "" "{ Mergefield LastName } { Mergefield Initial } { Mergefield Address1 } { Mergefield Address2 } { Mergefield City }, { Mergefield Prov } { Mergefield PostalCode } { Mergefield Country }" ""}" "" } This would test each record to see that it fits the criteria You could even save each of the four blocks of fields as autotext entries and add them to the first label of a blank merge label document and propagate then merge. Note that the fields are entered from the keyboard using CTRL+F9 for each pair of brackets. For other examples see http://www.gmayor.com/formatting_word_fields.htm Michael Koerner wrote: Using the Filter in Excel seems to be the best approach. I'm kinda doing that right now by having 4 different Excel sheets (LOL) I'll ask in the Excel group, thanks very much. "Peter Jamieson" wrote in message ... From your examples it's not completely clear what you're trying to do - Eliminate labels that are not for Canada? Or avoid printing the Country field if it's Canada? Or some combination of the two. Assuming you want to eliminate records, you really need to filter your records before you do the merge rather than use IF fields to try to eliminate them. If you're a regular Excel user, you may know how to do that easily in Excel and if so, that's probably the best approach. In Word... Open your mail merge main document. Enable the Mail Merge Toolbar (Tools|Customize). Then click the 3rd button (Mail Merge Recipients). Click the tiny dropdown at the top of the "Country" column. If you see "Canada" listed, select it. if not, click Advanced..." and use the form in the Query Options Dialog box to select Country Equals Canada and Email is not blank or whatever the appropriate criteria are. Word uses these criteria to construct a SQL Query but unfortunately it does not always get it right. If your criteria are more complicated than the Query Options dialog can manage you have to use VBA to issue the correct SQL query (which is one reason why it's better to filter in Excel if you can). Peter Jamieson "Michael Koerner" wrote in message ... Using MSOffice 2003 SP(2) I have the following Excel fields on a label page and it prints out all the labels just fine. LastName Initial Address1 Address2 City, Prov PostalCode Country I would like to be able to use the same Excel file and using I guess IF commands print the labels IF the country = Canada, print the labels IF the country Canada, print the labels IF the country = Canada and the email field is blank, print the labels IF the country Canada and the email field is blank, print the labels Any assistance will be greatly appreciated. -- Regards Michael Koerner |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Mail merge Labels
LOL, I asked in the Excel ng, and they said I should filter through Word.
Guess I'll stick with what I have got, a different Excel worksheet for each merge. -- Regards Michael Koerner "Graham Mayor" wrote in message ... On reflection - perhaps not. This would leave blank labels where the data does not match. Stick to the filtering -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org Graham Mayor wrote: While I agree with Peter about filtering in the data file, a possible alternative method would be to have four different merge documents each accessing the same data file. In this case the label would have Case 1 {IF {Mergefield Country} = "Canada" "{Mergefield LastName} {Mergefield Initial} {Mergefield Address1} {Mergefield Address2} {Mergefield City}, {Mergefield Prov} {Mergefield PostalCode} {Mergefield Country}"} Case 2 {IF {Mergefield Country} = "Canada" "{Mergefield LastName} {Mergefield Initial} {Mergefield Address1} {Mergefield Address2} {Mergefield City}, {Mergefield Prov} {Mergefield PostalCode} {Mergefield Country}"} Case 3 { IF { Mergefield Country } = "Canada" "{ IF { Mergefield Email } = "" "{ Mergefield LastName } { Mergefield Initial } { Mergefield Address1 } { Mergefield Address2 } { Mergefield City }, { Mergefield Prov } { Mergefield PostalCode } { Mergefield Country }" ""}" "" } Case 4 { IF { Mergefield Country } "Canada" "{ IF { Mergefield Email } = "" "{ Mergefield LastName } { Mergefield Initial } { Mergefield Address1 } { Mergefield Address2 } { Mergefield City }, { Mergefield Prov } { Mergefield PostalCode } { Mergefield Country }" ""}" "" } This would test each record to see that it fits the criteria You could even save each of the four blocks of fields as autotext entries and add them to the first label of a blank merge label document and propagate then merge. Note that the fields are entered from the keyboard using CTRL+F9 for each pair of brackets. For other examples see http://www.gmayor.com/formatting_word_fields.htm Michael Koerner wrote: Using the Filter in Excel seems to be the best approach. I'm kinda doing that right now by having 4 different Excel sheets (LOL) I'll ask in the Excel group, thanks very much. "Peter Jamieson" wrote in message ... From your examples it's not completely clear what you're trying to do - Eliminate labels that are not for Canada? Or avoid printing the Country field if it's Canada? Or some combination of the two. Assuming you want to eliminate records, you really need to filter your records before you do the merge rather than use IF fields to try to eliminate them. If you're a regular Excel user, you may know how to do that easily in Excel and if so, that's probably the best approach. In Word... Open your mail merge main document. Enable the Mail Merge Toolbar (Tools|Customize). Then click the 3rd button (Mail Merge Recipients). Click the tiny dropdown at the top of the "Country" column. If you see "Canada" listed, select it. if not, click Advanced..." and use the form in the Query Options Dialog box to select Country Equals Canada and Email is not blank or whatever the appropriate criteria are. Word uses these criteria to construct a SQL Query but unfortunately it does not always get it right. If your criteria are more complicated than the Query Options dialog can manage you have to use VBA to issue the correct SQL query (which is one reason why it's better to filter in Excel if you can). Peter Jamieson "Michael Koerner" wrote in message ... Using MSOffice 2003 SP(2) I have the following Excel fields on a label page and it prints out all the labels just fine. LastName Initial Address1 Address2 City, Prov PostalCode Country I would like to be able to use the same Excel file and using I guess IF commands print the labels IF the country = Canada, print the labels IF the country Canada, print the labels IF the country = Canada and the email field is blank, print the labels IF the country Canada and the email field is blank, print the labels Any assistance will be greatly appreciated. -- Regards Michael Koerner |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail merge to continuous labels skips labels between pages | Mailmerge | |||
Mail Merge - How do I set-up Home Phone & e-mail lines on labels? | Mailmerge | |||
Mail merge labels will not fully merge | Mailmerge | |||
How do I e-mail mail-merge made labels? | Mailmerge | |||
Word Labels into Mail Merge Labels | Mailmerge |