Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using MERGEFIELD "If-then-else" with part of a string
I have a question that may have an answer already, but I'm not sure if it's
the same thing. I am extracting a file from an Oracle database that contains a number of fields. This file is a csv with a .txt extension (as the extract process does not give the option of creating a file with a .csv extension). One of the fields is CHKL, which includes a concatenated list of checklist items that is separated by spaces. For example, the checklist items for a particular person might include the text "$30 Application Fee(space)GRE Scores(space)Letter of Reference", etc. What the end-user wants is to have text show in the merged document that specifically references the application fee ONLY IF the record has that text in the CHKL field. It is easy to do this in SQL with a "contains" in a WHERE clause. Unfortunately, Word 2003 doesn't let you do this. If anyone has any ideas on how I might accomplish this, I would greatly appreciate it. At this point, the merge document just has text pertaining to the application fee whether or not the CHKL field contains that item or not. They can live with this for the "go-live", but it would be nice and elegant to be able to do it right. Thanks so much, -- Michelle Craig Kent State University |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Using MERGEFIELD "If-then-else" with part of a string
There are a number of possible approaches to this kind of problem, e.g.
1. If you know in advance the precise criteria you need to be looking for, you can include an additional column in the SQL or View you use to extract data - I'd have to look up the correct Oracle SQL for that myself, but perhaps something like SELECT ...., contains(CHKL, 'Application Fee') as 'appfee', ... FROM ... Then in Word you should be able to use a nested IF field such as { IF { MERGEFIELD appfee } 0 "the text and fields you want if there is an appfee" "the text and fields you want if there isn't an appfee" } 2. Word's IF field can use wildcards in comparisons, but they are limited, particulary in the sense that you can't do a contains by matching "*Application Fee*" (the limitation is that you can't have the multicharacter wildcard "*" at both ends of the expression). Also, there are text/string length limitations in these comparisons. However, if you know that the Application Fee string is always at the beginning of the list, and if you know that the text before the term "Application Fee" can only be certain lengths - say, 4 or 5 characters, you may be able to use { IF { MERGEFIELD CHKL } = "????Application Fee*" "{ SET appfee 1 }" "{ IF { MERGEFIELD CHKL } = "?????Application Fee*" "{ SET appfee 1 }" "{ SET appfee 0 }" }" "{ SET appfee 0 }" }{ IF { REF appfee } 0 "the text and fields you want if there is an appfee" "the text and fields you want if there isn't an appfee" } 3. If your data source is a text file with 254 columns or fewer, you may be able to open it as a data source using the OLE DB provider, which uses the Jet (Access) database engine. In that case, you can use VBA and WOrd's OpenDataSource method to specify an SQL query that does much the same as option (1), i.e. creates a new column that makes it possible to use a Word IF field to do what you want. But let us know if you want o go that route, because unless you are using your .txt file for a number of different merges, I suspect you would be better off doing the query in Oracle. And don't forget that you can connect a Word Mail Merge Main document directly to an Oracle Table or View, or - if you are willing to use VBA - by specifying an Oracle SQL query in OpenDataSource (but the query length is limited to either 255 or 511 characters). Peter Jamieson "Michelle Craig" wrote in message ... I have a question that may have an answer already, but I'm not sure if it's the same thing. I am extracting a file from an Oracle database that contains a number of fields. This file is a csv with a .txt extension (as the extract process does not give the option of creating a file with a .csv extension). One of the fields is CHKL, which includes a concatenated list of checklist items that is separated by spaces. For example, the checklist items for a particular person might include the text "$30 Application Fee(space)GRE Scores(space)Letter of Reference", etc. What the end-user wants is to have text show in the merged document that specifically references the application fee ONLY IF the record has that text in the CHKL field. It is easy to do this in SQL with a "contains" in a WHERE clause. Unfortunately, Word 2003 doesn't let you do this. If anyone has any ideas on how I might accomplish this, I would greatly appreciate it. At this point, the merge document just has text pertaining to the application fee whether or not the CHKL field contains that item or not. They can live with this for the "go-live", but it would be nice and elegant to be able to do it right. Thanks so much, -- Michelle Craig Kent State University |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What does "char" "char1" "char2" mean in styles in Word? | Microsoft Word Help | |||
How to "lock" part of a document? | Microsoft Word Help | |||
The "Symbol" under "Insert" disappeared and replaced by "Number" | Microsoft Word Help | |||
How do I convert a number into a text string (5 into "Five")? | Microsoft Word Help | |||
What happened to the "blue" part on the side ruler in print layout | Page Layout |