Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using part of a field
Hello,
I have to prepare a letter and the client has requested that the full stop after a person's initial be removed. This is in our mainframe database like this and as such can't be removed from there. Is there a way to use part of a field or tell it to remove punctuation? I am using word 2000. Thanks, Chris |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using part of a field
Since you are probably connecting via MS Query and sending a SQL query to
the database, you ought to be able to modify the SQL that MS Query generates to remove any trailing ".", as long as the SQL dialect being used can do it. You can do that either while you are working in SQL Query, or directly using Word VBA and the OpenDataSource method - if so, I can give you some more hints but it would be helpful to know a. which database you're using and whether you're familiar with its dialect of SQL b. whether you are familiar with either MS Query or Word VBA If you would prefer to use Word fields, then you could use a lengthy set of IF fields as follows, but the exact fields would depend on exactly what the initial field can contain, e.g., which of the following can it contain: a. blank b. lowercase unaccented latin initial (a-z) c. uppercase unaccented latin initial (A-Z) d. (a), (b) or (c) followed by a period/full stop e. other characters, one or many, either followed by a full stop or not f. and so on... But assuming it's (c) followed by a period, you could try the following fields - all the {} need to be the special field braces you can insert using ctrl-F9, and you would need to substitute the name of your initial field where I've used myinitialfield { SET I { MERGEFIELD myinitialfield } }{ IF "{ I }" = "A*" "A" "" }{ IF "{ I }" = "B*" "B" "" }{ IF "{ I }" = "C*" "C" "" } and so on until }{ IF "{ I }" = "Z*" "Z" "" } Once you've created the pack of fields you need, you can save them to an Autotext (or another file) for re-use. I've used the wildcard "*" which matches multiple characters rather than "?" (matches one) or "." simple for flexibility. If the Database field could contain lower case but you actually want uppercase in your letter, you can use { MERGEFIELD myinitialfield \*Upper } in each comparison. There are other possible approaches... Peter Jamieson "Chris Stammers" wrote in message ... Hello, I have to prepare a letter and the client has requested that the full stop after a person's initial be removed. This is in our mainframe database like this and as such can't be removed from there. Is there a way to use part of a field or tell it to remove punctuation? I am using word 2000. Thanks, Chris |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using part of a field
Dear Peter,
The data source is an AS400 system. The letter template is set up just to bring in the relevant fields, with some switching or formatting where necessary. I believe the name field in question is a 'label' field so contains both firstname and surname. The problem, I think, with using each letter of the alphabet as a search criteria is that in some cases there may be more than one initial so the query wouldn't work in that case would it? I'm not familiar with MS Query or Word VBA. I will see if the fields are broken down anywhere first as I am sure that it is just that label field that is the problem. Thanks for your help. Regards, Chris "Peter Jamieson" wrote: Since you are probably connecting via MS Query and sending a SQL query to the database, you ought to be able to modify the SQL that MS Query generates to remove any trailing ".", as long as the SQL dialect being used can do it. You can do that either while you are working in SQL Query, or directly using Word VBA and the OpenDataSource method - if so, I can give you some more hints but it would be helpful to know a. which database you're using and whether you're familiar with its dialect of SQL b. whether you are familiar with either MS Query or Word VBA If you would prefer to use Word fields, then you could use a lengthy set of IF fields as follows, but the exact fields would depend on exactly what the initial field can contain, e.g., which of the following can it contain: a. blank b. lowercase unaccented latin initial (a-z) c. uppercase unaccented latin initial (A-Z) d. (a), (b) or (c) followed by a period/full stop e. other characters, one or many, either followed by a full stop or not f. and so on... But assuming it's (c) followed by a period, you could try the following fields - all the {} need to be the special field braces you can insert using ctrl-F9, and you would need to substitute the name of your initial field where I've used myinitialfield { SET I { MERGEFIELD myinitialfield } }{ IF "{ I }" = "A*" "A" "" }{ IF "{ I }" = "B*" "B" "" }{ IF "{ I }" = "C*" "C" "" } and so on until }{ IF "{ I }" = "Z*" "Z" "" } Once you've created the pack of fields you need, you can save them to an Autotext (or another file) for re-use. I've used the wildcard "*" which matches multiple characters rather than "?" (matches one) or "." simple for flexibility. If the Database field could contain lower case but you actually want uppercase in your letter, you can use { MERGEFIELD myinitialfield \*Upper } in each comparison. There are other possible approaches... Peter Jamieson "Chris Stammers" wrote in message ... Hello, I have to prepare a letter and the client has requested that the full stop after a person's initial be removed. This is in our mainframe database like this and as such can't be removed from there. Is there a way to use part of a field or tell it to remove punctuation? I am using word 2000. Thanks, Chris |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using part of a field
The problem, I think, with using each
letter of the alphabet as a search criteria is that in some cases there may be more than one initial so the query wouldn't work in that case would it? Correct. The thing is to find out what the possible contents are and what exactly you need to extract. I don't know what facilities AS400 SQL has for extracting text from strings but if it doesn't have the necessary then there are other possible ways to do it. Peter Jamieson "Chris Stammers" wrote in message ... Dear Peter, The data source is an AS400 system. The letter template is set up just to bring in the relevant fields, with some switching or formatting where necessary. I believe the name field in question is a 'label' field so contains both firstname and surname. The problem, I think, with using each letter of the alphabet as a search criteria is that in some cases there may be more than one initial so the query wouldn't work in that case would it? I'm not familiar with MS Query or Word VBA. I will see if the fields are broken down anywhere first as I am sure that it is just that label field that is the problem. Thanks for your help. Regards, Chris "Peter Jamieson" wrote: Since you are probably connecting via MS Query and sending a SQL query to the database, you ought to be able to modify the SQL that MS Query generates to remove any trailing ".", as long as the SQL dialect being used can do it. You can do that either while you are working in SQL Query, or directly using Word VBA and the OpenDataSource method - if so, I can give you some more hints but it would be helpful to know a. which database you're using and whether you're familiar with its dialect of SQL b. whether you are familiar with either MS Query or Word VBA If you would prefer to use Word fields, then you could use a lengthy set of IF fields as follows, but the exact fields would depend on exactly what the initial field can contain, e.g., which of the following can it contain: a. blank b. lowercase unaccented latin initial (a-z) c. uppercase unaccented latin initial (A-Z) d. (a), (b) or (c) followed by a period/full stop e. other characters, one or many, either followed by a full stop or not f. and so on... But assuming it's (c) followed by a period, you could try the following fields - all the {} need to be the special field braces you can insert using ctrl-F9, and you would need to substitute the name of your initial field where I've used myinitialfield { SET I { MERGEFIELD myinitialfield } }{ IF "{ I }" = "A*" "A" "" }{ IF "{ I }" = "B*" "B" "" }{ IF "{ I }" = "C*" "C" "" } and so on until }{ IF "{ I }" = "Z*" "Z" "" } Once you've created the pack of fields you need, you can save them to an Autotext (or another file) for re-use. I've used the wildcard "*" which matches multiple characters rather than "?" (matches one) or "." simple for flexibility. If the Database field could contain lower case but you actually want uppercase in your letter, you can use { MERGEFIELD myinitialfield \*Upper } in each comparison. There are other possible approaches... Peter Jamieson "Chris Stammers" wrote in message ... Hello, I have to prepare a letter and the client has requested that the full stop after a person's initial be removed. This is in our mainframe database like this and as such can't be removed from there. Is there a way to use part of a field or tell it to remove punctuation? I am using word 2000. Thanks, Chris |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using part of a field
Thanks Peter. I was hoping that you could use something like a { MERGEFIELD
Name/R,1} - in other words, take the first character on the right (or left) - or similar just to get the first letter and leave out anything else in the string. I'm used to SQL code (FoxPro in particular) where you can do that sort of thing in a query. "Peter Jamieson" wrote: The problem, I think, with using each letter of the alphabet as a search criteria is that in some cases there may be more than one initial so the query wouldn't work in that case would it? Correct. The thing is to find out what the possible contents are and what exactly you need to extract. I don't know what facilities AS400 SQL has for extracting text from strings but if it doesn't have the necessary then there are other possible ways to do it. Peter Jamieson "Chris Stammers" wrote in message ... Dear Peter, The data source is an AS400 system. The letter template is set up just to bring in the relevant fields, with some switching or formatting where necessary. I believe the name field in question is a 'label' field so contains both firstname and surname. The problem, I think, with using each letter of the alphabet as a search criteria is that in some cases there may be more than one initial so the query wouldn't work in that case would it? I'm not familiar with MS Query or Word VBA. I will see if the fields are broken down anywhere first as I am sure that it is just that label field that is the problem. Thanks for your help. Regards, Chris "Peter Jamieson" wrote: Since you are probably connecting via MS Query and sending a SQL query to the database, you ought to be able to modify the SQL that MS Query generates to remove any trailing ".", as long as the SQL dialect being used can do it. You can do that either while you are working in SQL Query, or directly using Word VBA and the OpenDataSource method - if so, I can give you some more hints but it would be helpful to know a. which database you're using and whether you're familiar with its dialect of SQL b. whether you are familiar with either MS Query or Word VBA If you would prefer to use Word fields, then you could use a lengthy set of IF fields as follows, but the exact fields would depend on exactly what the initial field can contain, e.g., which of the following can it contain: a. blank b. lowercase unaccented latin initial (a-z) c. uppercase unaccented latin initial (A-Z) d. (a), (b) or (c) followed by a period/full stop e. other characters, one or many, either followed by a full stop or not f. and so on... But assuming it's (c) followed by a period, you could try the following fields - all the {} need to be the special field braces you can insert using ctrl-F9, and you would need to substitute the name of your initial field where I've used myinitialfield { SET I { MERGEFIELD myinitialfield } }{ IF "{ I }" = "A*" "A" "" }{ IF "{ I }" = "B*" "B" "" }{ IF "{ I }" = "C*" "C" "" } and so on until }{ IF "{ I }" = "Z*" "Z" "" } Once you've created the pack of fields you need, you can save them to an Autotext (or another file) for re-use. I've used the wildcard "*" which matches multiple characters rather than "?" (matches one) or "." simple for flexibility. If the Database field could contain lower case but you actually want uppercase in your letter, you can use { MERGEFIELD myinitialfield \*Upper } in each comparison. There are other possible approaches... Peter Jamieson "Chris Stammers" wrote in message ... Hello, I have to prepare a letter and the client has requested that the full stop after a person's initial be removed. This is in our mainframe database like this and as such can't be removed from there. Is there a way to use part of a field or tell it to remove punctuation? I am using word 2000. Thanks, Chris |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using part of a field
{ MERGEFIELD
Name/R,1} Unfortunately not so easily. Sometimes it's possible to use "tricks" with fields to extract results, but in this case none springs to mind, and I would guess that macropod doesn't have anything up his sleeve on this one either. IMO the best approach is probably as follows: If you can work out the necessary AS400 SQL code you can issue it in a VBA OpenDataSource, which will look something like Sub SetUpDataSource() ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:="DSN=your_AS400_ODBC_DSN_name;", _ SQLStatement:="SELECT *,left(your_field_name,1) AS `leftchar` FROM AS400TableName" End Sub if you are using a "machine" DSN or Sub SetUpDataSource() ActiveDocument.MailMerge.OpenDataSource _ Name:="the full path name of your file DSN", _ Connection:="FILEDSN=the full path name of your file DSN;", _ SQLStatement:="SELECT *,left(your_field_name,1) AS `leftchar` FROM AS400TableName" End Sub if you are using a File DSN. AS400 SQL may have a different way of doing substrings, e.g. substring(your_field_name,1,1) or some such. I can't tell you the precise syntax for these parameters without an AS400 to hand, but you can base what you do on the existing values which you should be able to get by running the following VBA with your mail merge main document open and its data source attached: Sub GetDataSourceParameters() With ActiveDocument.MailMerge.DataSource Debug.Print .Name Debug.Print .ConnectString Debug.Print .QueryString End With You may need to look at http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm and enable the Immediate Window in the VB Editor (use ctrl-G) to see the results of those Debug.Print statements. You should only need to run Sub SetUpDataSource() once, then save and close the mail merge main document. However, a. in my experience getting OpenDataSource to work is not always a trivial exercise b. if the users are allowed to set sort and filter options, the approach will fall to pieces. Another approach may be to use a DATABASE field. As long the ODBC desktop database drivers are installed on the system doing the merge, you can do it the following way. However, in this case it will only work if there are no ' and probably " characters in the field you are inspecting (and if the field contains surnames there are likely to be ' characters, e.g. in Irish surnames starting with O' ). If you can use the DATABASE field with FoxPro SQL, you might prefer to do it that way. But this is what you need for Jet: Create a Windows folder - let's call it c:\i In that folder, create a plain text file (you can edit it with Notepad) called i.dsn , containing the followiing text: [ODBC] DRIVER=Microsoft Text Driver (*.txt; *.csv) FIL=text DriverId=27 DefaultDir=C:\i (Setting DefaultDir to the folder that the i.dsn file is in ensures the existence of the folder :-) ) Insert the follwing nested fields in your document: { DATABASE \d "c:\\i\\i.dsn" \c "FILEDSN=c:\\i\\i.dsn" \s "SELECT left('{ MERGEFIELD your_field_name }',1)" } Execute it and you should see the single character that you want. The reason this can't work if your data contains ' marks is because the MERGEFIELD field would then contain them and left('text with' in it',1) would be invalid syntactically. Unfortunately, this is probably not quite enough, because the DATABASE field has been changed at some point so that it sometimes inserts a paragraph mark before the results of the SELECT. In this case we can use a horrible kludge: { SYMBOL { DATABASE \d "c:\\i\\i.dsn" \c "FILEDSN=c:\\i\\i.dsn" \s "SELECT asc(left('{ MERGEFIELD your_field_name } ' ,1))" \#0 } If you only ever want left(something,1) you can just use { SYMBOL { DATABASE \d "c:\\i\\i.dsn" \c "FILEDSN=c:\\i\\i.dsn" \s "SELECT asc('{ MERGEFIELD your_field_name } ' )" \#0 } NB, - the space after { MERGEFIELD your_field_name } is deliberate and is there to deal with the case where the field is empty - you need to double up the backslashes in the file names, or use single forward slashes: "c:/i/i.dsn" If you need to extract other items from the field for example the first word, you typically have to use constructions using instr, e.g. SELECT left('{ MERGEFIELD your_field_name } ', instr('{ MERGEFIELD your_field_name } ',' ')-1) Best I can do, Peter Jamieson "Chris Stammers" wrote in message ... Thanks Peter. I was hoping that you could use something like a { MERGEFIELD Name/R,1} - in other words, take the first character on the right (or left) - or similar just to get the first letter and leave out anything else in the string. I'm used to SQL code (FoxPro in particular) where you can do that sort of thing in a query. "Peter Jamieson" wrote: The problem, I think, with using each letter of the alphabet as a search criteria is that in some cases there may be more than one initial so the query wouldn't work in that case would it? Correct. The thing is to find out what the possible contents are and what exactly you need to extract. I don't know what facilities AS400 SQL has for extracting text from strings but if it doesn't have the necessary then there are other possible ways to do it. Peter Jamieson "Chris Stammers" wrote in message ... Dear Peter, The data source is an AS400 system. The letter template is set up just to bring in the relevant fields, with some switching or formatting where necessary. I believe the name field in question is a 'label' field so contains both firstname and surname. The problem, I think, with using each letter of the alphabet as a search criteria is that in some cases there may be more than one initial so the query wouldn't work in that case would it? I'm not familiar with MS Query or Word VBA. I will see if the fields are broken down anywhere first as I am sure that it is just that label field that is the problem. Thanks for your help. Regards, Chris "Peter Jamieson" wrote: Since you are probably connecting via MS Query and sending a SQL query to the database, you ought to be able to modify the SQL that MS Query generates to remove any trailing ".", as long as the SQL dialect being used can do it. You can do that either while you are working in SQL Query, or directly using Word VBA and the OpenDataSource method - if so, I can give you some more hints but it would be helpful to know a. which database you're using and whether you're familiar with its dialect of SQL b. whether you are familiar with either MS Query or Word VBA If you would prefer to use Word fields, then you could use a lengthy set of IF fields as follows, but the exact fields would depend on exactly what the initial field can contain, e.g., which of the following can it contain: a. blank b. lowercase unaccented latin initial (a-z) c. uppercase unaccented latin initial (A-Z) d. (a), (b) or (c) followed by a period/full stop e. other characters, one or many, either followed by a full stop or not f. and so on... But assuming it's (c) followed by a period, you could try the following fields - all the {} need to be the special field braces you can insert using ctrl-F9, and you would need to substitute the name of your initial field where I've used myinitialfield { SET I { MERGEFIELD myinitialfield } }{ IF "{ I }" = "A*" "A" "" }{ IF "{ I }" = "B*" "B" "" }{ IF "{ I }" = "C*" "C" "" } and so on until }{ IF "{ I }" = "Z*" "Z" "" } Once you've created the pack of fields you need, you can save them to an Autotext (or another file) for re-use. I've used the wildcard "*" which matches multiple characters rather than "?" (matches one) or "." simple for flexibility. If the Database field could contain lower case but you actually want uppercase in your letter, you can use { MERGEFIELD myinitialfield \*Upper } in each comparison. There are other possible approaches... Peter Jamieson "Chris Stammers" wrote in message ... Hello, I have to prepare a letter and the client has requested that the full stop after a person's initial be removed. This is in our mainframe database like this and as such can't be removed from there. Is there a way to use part of a field or tell it to remove punctuation? I am using word 2000. Thanks, Chris |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy/paste part of text in a form field | Microsoft Word Help | |||
Part protected, part unprotected? | Tables | |||
In forms, how can I select part of a form field | Tables | |||
Date formatting in text form field, part II | Microsoft Word Help | |||
How do I insert and format part of a data field in a Word document | Microsoft Word Help |