none
Format Field Code result with mixed alpha and numeric characters RRS feed

  • Question

  • I have merge documents where the code for the policy number inserts an alpha two digit prefix, a hyphen and then a 6-digit number, e.b. AB-12345. I need to get this to merge without the hyphen and produce only AB12345. Is there some instruction or switch I can use? The numbers come from a data base and cannot be altered before the merge. Thanks.

    PMS

    Wednesday, January 30, 2013 1:10 PM

Answers

  • You have 4 main options:
     a. find some way to alter the code on the way from the database. You can usually issue SQL in the VBA OpenDataSource to do that, although sometimes it can cause problems with other fields, particularly memo fields. But for a Jet database where your field is called "myfield" and your table is called "mytable" would be something like this:

    SELECT left(m.[myfield],2) & mid(m.[myfield],4) As [mynewfield], m.* from [mytable] m

     b. use fields. Sure, 52 IF fields isn't very nice but it's not that unwieldy and it's pretty easy to understand what they are doing. As long as you are definitely restricted to uppercase alpha A-Z, or you don't care that the resulting letters are uppercase, no accented characters etc., you need something like this
    { SET c { MERGEFIELD myfield } }
    { IF "{ REF c }" = "A*" "A" }{ IF "{ REF c }" = "B*" "B" }...etc. then
    { IF "{ REF c }" = "?A*" "A" }{ IF "{ REF c }" = "?B*" "B" }...etc.
    then in this case you can probably get the numeric part using { =ABS(c) \#000000 }

    All the {} need to be the special field code braces you can insert using ctrl-F9. In fact in this case I think you can reduce the syntax of the IF fields to, e.g.
    { IF c = A* A } etc. But  { =ABS({ MERGEFIELD my field }) \#000000 } won't work.

    If you need to check for lowercase as well as uppercase, but you only need to display/print uppercase, you can do 
    { IF { REF c \*Upper } = "A*" "A" } and so on.

     c. Use VBA and Word's mailmerge events to construct a document variable - let's call it myvar containing the result of left(my field,2) & mid(my field,4), which would then be inserted in the document using { DOCVARIABLE myvar }

     d. add identifying markers to the output, merge to a new document, then strip the markers using VBA.


    Peter Jamieson

    • Marked as answer by PMSS Friday, February 1, 2013 4:20 PM
    Thursday, January 31, 2013 8:05 PM
  • There are always only two uppercase alpha characters followed by the dash (-), and then five numeric characters.

    PMS

    • Marked as answer by PMSS Friday, February 1, 2013 4:19 PM
    Friday, February 1, 2013 12:34 PM

All replies

  • That is not practical with field coding. Although isolating the numeric portion is quite easy, retrieving the letters would require 26 IF tests for each character - and that's assuming you have only upper-case letters.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, January 30, 2013 11:50 PM
  • Hi PMS

    I agree with Paul. Any chance you can put a query (or stored procedure) between the data table and the mail merge? So that the query can prepare the data without affecting the underlying table and you can merge to the query? Most database-internal query languages support functions that will let you manipulate strings, but these have to be run by the database...


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, January 31, 2013 7:22 AM
    Moderator
  • You have 4 main options:
     a. find some way to alter the code on the way from the database. You can usually issue SQL in the VBA OpenDataSource to do that, although sometimes it can cause problems with other fields, particularly memo fields. But for a Jet database where your field is called "myfield" and your table is called "mytable" would be something like this:

    SELECT left(m.[myfield],2) & mid(m.[myfield],4) As [mynewfield], m.* from [mytable] m

     b. use fields. Sure, 52 IF fields isn't very nice but it's not that unwieldy and it's pretty easy to understand what they are doing. As long as you are definitely restricted to uppercase alpha A-Z, or you don't care that the resulting letters are uppercase, no accented characters etc., you need something like this
    { SET c { MERGEFIELD myfield } }
    { IF "{ REF c }" = "A*" "A" }{ IF "{ REF c }" = "B*" "B" }...etc. then
    { IF "{ REF c }" = "?A*" "A" }{ IF "{ REF c }" = "?B*" "B" }...etc.
    then in this case you can probably get the numeric part using { =ABS(c) \#000000 }

    All the {} need to be the special field code braces you can insert using ctrl-F9. In fact in this case I think you can reduce the syntax of the IF fields to, e.g.
    { IF c = A* A } etc. But  { =ABS({ MERGEFIELD my field }) \#000000 } won't work.

    If you need to check for lowercase as well as uppercase, but you only need to display/print uppercase, you can do 
    { IF { REF c \*Upper } = "A*" "A" } and so on.

     c. Use VBA and Word's mailmerge events to construct a document variable - let's call it myvar containing the result of left(my field,2) & mid(my field,4), which would then be inserted in the document using { DOCVARIABLE myvar }

     d. add identifying markers to the output, merge to a new document, then strip the markers using VBA.


    Peter Jamieson

    • Marked as answer by PMSS Friday, February 1, 2013 4:20 PM
    Thursday, January 31, 2013 8:05 PM
  •  But  { =ABS({ MERGEFIELD my field }) \#000000 } won't work.

    If you need to check for lowercase as well as uppercase, but you only need to display/print uppercase, you can do 
    { IF { REF c \*Upper } = "A*" "A" } and so on.

    Hi Peter,

    For the numeric part, you could use: {=ABS({MERGEFIELD MyField \# 0})}

    Of course if the OP needs to print the letters in the case in which they appear, the field count for the letters just doubled, to 104 - and that assumes there's never more than two letters.

    If there can be letters after the numbers, yet more such fields would be required and, if there could be numbers in the prefix and/or letters interspersed amongst the numbers in the suffix, you could end up having 36 (or more) IF fields for every possible character position.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Thursday, January 31, 2013 9:26 PM
  • FWIW I thought that the OP could probably use some concrete examples, and then it's up to them to decide what's workable. I agree that even 52 is a lot of IF fields, but if the problem is the simpler one (always two A-Z characters, always "-", no trailing characters we haven't been told about, then it is at least straightforward. In the simplest case - let's say it's only ever uppercase A-Z - , you can simplify the coding by repeating the same IF field 26 times for each letter, e.g.

    { IF c = "{ SEQ C1 \*Alphabetic }*" "{ SEQ C1 \c \*Alphabetic }" }{ IF c = "{ SEQ C1 \*Alphabetic }*" "{ SEQ C1 \c \*Alphabetic }" } etc.

    and

    { IF c = "?{ SEQ C2 \*Alphabetic }*" "{ SEQ C2 \c \*Alphabetic }" }{ IF c = "?{ SEQ C2 \*Alphabetic }*" "{ SEQ C2 \c \*Alphabetic }" } etc.

    More easily inserted, but not so great if you have to understand it for maintenance.


    Peter Jamieson

    Thursday, January 31, 2013 10:37 PM
  • There are always only two uppercase alpha characters followed by the dash (-), and then five numeric characters.

    PMS

    • Marked as answer by PMSS Friday, February 1, 2013 4:19 PM
    Friday, February 1, 2013 12:34 PM
  • In that case, any of the suggested field-based approaches should work.

    Peter Jamieson

    Friday, February 1, 2013 3:44 PM
  • Thanks everyone for your suggestions/ideas. I've got the forms working in my test environment. I have less than 15 alpha policy number prefixes and most prefix types have their own forms, so at most I would only need to repeat the statement twice in a few of the docs. Here's what worked for me: {SET c {MERGEFIELD 80}}{if "{ref C}"="AB*" "AB"}{=ABS(c)\#000000} (Where MERGEFIELD 80 is the policy number code and AB would be one of the policy prefixes.) This saves a ton of phone calls from the insureds! Paula

    PMS

    Friday, February 1, 2013 4:19 PM