Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mailmerge - sort field contents and convert text (2000 and 2002)

    Hi,

    I'm creating a mail merge that uses a .dbf file and its associated .dbt file as its data source.

    I need to accomplish two things:

    1. The text inside one of the merge fields needs to be sorted
    2. Another field contains encoded text that I need to decode. I know the algorithm and can create a VBScript, but I don't know how to place it in the merge document.

    Thanks in advance of any help.

    Jim

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Mailmerge - sort field contents and convert text (2000 and 2002)

    My advice would be to take the data to either Excel or to a Word table - Excel would be preferrable as you can easily write formulae to decode the data. By sorting, I assume you mean the data records should be sorted on the specified text field, and you can do that in Excel or in Word. It is probably possible to get an ODBC driver for the dbf/dbt files and use the native files as a data source for the merge, but it would require lots of tinkering compared to importing the data into Excel.
    Wendell

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailmerge - sort field contents and convert text (2000 and 2002)

    Hi Wendell,

    Thanks for the response.

    The sort actually has to occur on text inside a field. Maybe it would help if I explain. There are two main columns of importance in the DBF. One contains an encrypted username, and the other column contains a list of keywords associated to that user. The keywords are unsorted within the field, separated by embedded carriage returns. So, the sort isn't on the column itself - had that been the case, I could have used an "Order By" clause in a SQL Select. To look at some sample data in CSV format, it would be as follows:

    "FGTE","RED

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mailmerge - sort field contents and convert text (2000 and 2002)

    This table structure is difficult to work with. A decent structure would consist of a separate record for each user/keyword combination. I would convert the data into Access. If possible, do it once and for all, otherwise, perform the conversion on the fly. This can be done using VBA with DAO or ADO, by looping through a recordset based on the table, and within each record looping through the lines of the keyword field.
    To present the data, you would create a multi-column report grouped by user name, or a main report displaying the user names, with a multi-column subreport displaying the keywords (that would have to be across-then-down, however, snaking columns in subreports don't work.)

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mailmerge - sort field contents and convert text (2000 and 2002)

    Hi Hans,

    Unfortunately, I cannot change the source of the data. It is created and maintained in a third-party application.

    Jim

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mailmerge - sort field contents and convert text (2000 and 2002)

    In that case, I would link the data in an Access database, and create a temporary table using code, each time you need the report.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •