{ MERGEFIELD

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