Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If then else containing value not at the end or in the beginning
I've a question about a word 2000 mail merge :
a customer of ours is trying to create a document with an IF THEN ELSE statement. What he likes to do is the following : he wants to test if a field contains a certain value. I know it works fine when you do this : { IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""} or { IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""} So the word "verandering" has to be in the beginning (cfr. verandering*) or at the end (cfr. *verandering). But : it's possible that the word "verandering" is somewhere inbetween. And it seems that trying { IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is not working. Do you have any tips ? Thanks in advance ! |
#2
|
|||
|
|||
Yes, you can't use asterisks at both ends. I do not believe there is a way
to combine multiple IF statements to do this, either. You have to consider doing this in your data source, e.g. via a Query in Access that uses iif and instr functions. Peter Jamieson "Gert Raes" wrote in message ... I've a question about a word 2000 mail merge : a customer of ours is trying to create a document with an IF THEN ELSE statement. What he likes to do is the following : he wants to test if a field contains a certain value. I know it works fine when you do this : { IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""} or { IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""} So the word "verandering" has to be in the beginning (cfr. verandering*) or at the end (cfr. *verandering). But : it's possible that the word "verandering" is somewhere inbetween. And it seems that trying { IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is not working. Do you have any tips ? Thanks in advance ! |
#3
|
|||
|
|||
Nope, I've tried nested If statements but that doesn't do the job.
For those who want to give it a shot : - the datasource is a tab delimited textfile (I can't help it, that's how our programmers decided it) - the Mergefield M_148 can contain multiple lines. If so, each line will be separated by a ¶ TIA, Gert "Peter Jamieson" schreef: Yes, you can't use asterisks at both ends. I do not believe there is a way to combine multiple IF statements to do this, either. You have to consider doing this in your data source, e.g. via a Query in Access that uses iif and instr functions. Peter Jamieson "Gert Raes" wrote in message ... I've a question about a word 2000 mail merge : a customer of ours is trying to create a document with an IF THEN ELSE statement. What he likes to do is the following : he wants to test if a field contains a certain value. I know it works fine when you do this : { IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""} or { IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""} So the word "verandering" has to be in the beginning (cfr. verandering*) or at the end (cfr. *verandering). But : it's possible that the word "verandering" is somewhere inbetween. And it seems that trying { IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is not working. Do you have any tips ? Thanks in advance ! |
#4
|
|||
|
|||
There are a few things you can consider, and they all suffer from problems.
The three main approaches a a. preprocess your data in some way to give you the data source you need b. issue the query you need in Word VBA using OpenDataSource c. use a DATABASE field to return the item you need. There are potentially many ways to do (a) - ideally, your programmers would give you the data you needed but you must judge how difficult that is :-) Unfortunately (b) and (c) will only work if the connection to the data uses ODBC, and you have the necessary bits of the Jet (Access) database engine on the system that is doing the merge. If you do, or it is relatively simple to set that up, you can try the following: a. set up an ODBC machine DSN for text files, if there isn't one already (have a look around Control Panel or Control Panel|Administrative Tools for the ODBC tool to do this). Unfortunately, in this case, you may also need to set up an entry for this file to specify that it is tab-delimied (otherwise in some cases, Word will treat each record as a single field.). I can go into this if you need, or search this group using google groups for jamieson odbc schema.ini and you may find something. b.open your mail merge main document and disconnect from the data source. Then connect, and check "Select method" in the Open Data Source dialog box. You should be prompted for the connection method to use - choose the one with "ODBC" in it. c. verify that your merge works as it should using ODBC. It may, for example, treat multiline text differently, and numbers and dates may be displayed differently. If it doesn't do enough of what you need, abandon this approach. d. Now you either need some VBA, or you need a DATABASE field. The former is preferable, but perhaps a bit less flexible. Also, if you are not familiar with VBA you could try the latter to see if it works. The VBA approach: e. create a VBA sub as follows, run it /once/, and save/close your document (notice that once Word has done the OpenDataSource, the connection and the SQL are defined, they are retained, so you do not have to keep this code in the document or a template except for maintenance purposes). Let us suppose your ODBC DSN is claled "Text Files", your data file is called "mydata.txt", and is in a folder called "c:\mydata" Substitute the various pieces of text marked with the values specific to your system (and remove the ) Sub MyConnect() With ActiveDocument.MailMerge ' disconnect the existing data source .MainDocumentType = wdNotAMergeDocument ' substitute the document type you need .MainDocumentType = wdFormLetters .OpenDataSource _ Name:="", _ Connection:="DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;", _ SQLStatement:="SELECT * FROM mydata.txt" End With End Sub Run that, and see if the merge document is still working properly. If not, there is probably a mistake in the code. When it is OK, change the code to Sub MyConnect() With ActiveDocument.MailMerge ' disconnect the existing data source .MainDocumentType = wdNotAMergeDocument ' substitute the document type you need .MainDocumentType = wdFormLetters .OpenDataSource _ Name:="", _ Connection:="DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;", _ SQLStatement:="SELECT *, iif(instr(1,M_148,'verandering')0,M_148,'') AS M_148A FROM mydata.txt" End With End Sub This should create a data source with an additional column called M_148A that contains M_148 if the text "verandering" is found in it and blank if not. If you need to test a lot of fields in this way, you will run out of space in SQLSattement (it is limited to 255 characters). You can extend the code to SQLStatement1, but only to around 511 characters. if you have a very large number of columns in your file, you probably won't be able to connect using ODBC anyway. The DATABASE field approach: For this, you need ato be able to select individual records in your data source using SQL, i.e. your data needs a unique primary key, which could be one field or several. Here' I assume you have a field called K containing a unique sequence number. Enable the Database toolbar in Word, and click the Insert Database button. Select your data source as above, selecting the ODBC method, and insert the data as a field. You should end up with a field that looks something like { DATABASE \c "DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;" \s "SELECT * FROM mydata.txt" \h } (Actually there will be much more in the \c parameter but you can remove some parts of it if you want). Select the field and re-execute it to make sure it works. Remove the \h and Modify the SELECT so you have { DATABASE \c "DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;" \s "SELECT M_148 FROM mydata.txt WHERE k = { MERGEFIELD k }" } (If your k is a text field, you will need to put single quotes around { MERGEFIELD k } ). he {} should be inserted using ctrl-F9 as usual. Re-execute the field. You should see the value of M_148 for the current record being previewed. Change the SELECT again so you have { DATABASE \c "DSN=Text Files;DBQ=c:\mydata;DriverID=27;FIL=text;" \s "SELECT iif(instr(1,M_148,'verandering')0,M_148,'') FROM mydata.txt WHERE k = { MERGEFIELD k }" } and re-execute the field. You should see the result you need. Notice that in later Word SPs, Word does not re-evaluate the DATABASE field automatically when you preview data and move from record to record, unless you have made the relevant change in the registry, but when you actually perform the merge you should still see the correct results. However, again, if you have lots of these tests to make, you will have lots of DATABASE fields, and I am not sure that Word will cope with the number of connections required. Peter Jamieson "Gert Raes" wrote in message ... Nope, I've tried nested If statements but that doesn't do the job. For those who want to give it a shot : - the datasource is a tab delimited textfile (I can't help it, that's how our programmers decided it) - the Mergefield M_148 can contain multiple lines. If so, each line will be separated by a ¶ TIA, Gert "Peter Jamieson" schreef: Yes, you can't use asterisks at both ends. I do not believe there is a way to combine multiple IF statements to do this, either. You have to consider doing this in your data source, e.g. via a Query in Access that uses iif and instr functions. Peter Jamieson "Gert Raes" wrote in message ... I've a question about a word 2000 mail merge : a customer of ours is trying to create a document with an IF THEN ELSE statement. What he likes to do is the following : he wants to test if a field contains a certain value. I know it works fine when you do this : { IF {MERGEFIELD M_148} = "verandering*" "{MERGEFIELD M_201}" ""} or { IF {MERGEFIELD M_148} = "*verandering" "{MERGEFIELD M_201}" ""} So the word "verandering" has to be in the beginning (cfr. verandering*) or at the end (cfr. *verandering). But : it's possible that the word "verandering" is somewhere inbetween. And it seems that trying { IF {MERGEFIELD M_148} = "*verandering*" "{MERGEFIELD M_201}" ""}, is not working. Do you have any tips ? Thanks in advance ! |