Re: Using part of a field

Thanks ofr all your help Peter. I will have a talk to our AS400 guys and have
a play with it all.

Regards,
Chris

"Peter Jamieson" wrote:

{ MERGEFIELD
Name/R,1}

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

"Chris Stammers" <ChrisStammers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4B10A857-3756-49C5-BE76-45D31118DF0B@xxxxxxxxxxxxxxxx
Thanks Peter. I was hoping that you could use something like a {
MERGEFIELD
Name/R,1} - in other words, take the first character on the right (or
left) -
or similar just to get the first letter and leave out anything else in the
string. I'm used to SQL code (FoxPro in particular) where you can do that
sort of thing in a query.



"Peter Jamieson" wrote:

The problem, I think, with using each
letter of the alphabet as a search criteria is that in some cases there
may
be more than one initial so the query wouldn't work in that case would
it?

Correct. The thing is to find out what the possible contents are and what
exactly you need to extract. I don't know what facilities AS400 SQL has
for
extracting text from strings but if it doesn't have the necessary then
there
are other possible ways to do it.

Peter Jamieson

"Chris Stammers" <ChrisStammers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:3ED537B3-B088-4319-A18B-8621157CCFF9@xxxxxxxxxxxxxxxx
Dear Peter,

The data source is an AS400 system. The letter template is set up just
to
bring in the relevant fields, with some switching or formatting where
necessary. I believe the name field in question is a 'label' field so
contains both firstname and surname. The problem, I think, with using
each
letter of the alphabet as a search criteria is that in some cases there
may
be more than one initial so the query wouldn't work in that case would
it?
I'm not familiar with MS Query or Word VBA. I will see if the fields
are
broken down anywhere first as I am sure that it is just that label
field
that
is the problem.

Thanks for your help.

Regards,
Chris

"Peter Jamieson" wrote:

Since you are probably connecting via MS Query and sending a SQL query
to
the database, you ought to be able to modify the SQL that MS Query
generates
to remove any trailing ".", as long as the SQL dialect being used can
do
it.

You can do that either while you are working in SQL Query, or directly
using
Word VBA and the OpenDataSource method - if so, I can give you some
more
hints but it would be helpful to know
a. which database you're using and whether you're familiar with its
dialect
of SQL
b. whether you are familiar with either MS Query or Word VBA

If you would prefer to use Word fields, then you could use a lengthy
set
of
IF fields as follows, but the exact fields would depend on exactly
what
the
initial field can contain, e.g., which of the following can it
contain:
a. <blank>
b. lowercase unaccented latin initial (a-z)
c. uppercase unaccented latin initial (A-Z)
d. (a), (b) or (c) followed by a period/full stop
e. other characters, one or many, either followed by a full stop or
not
f. and so on...

But assuming it's (c) followed by a period, you could try the
following
fields - all the {} need to be the special field braces you can insert
using
ctrl-F9, and you would need to substitute the name of your initial
field
where I've used myinitialfield

{ SET I { MERGEFIELD myinitialfield }
}{ IF "{ I }" = "A*" "A" ""
}{ IF "{ I }" = "B*" "B" ""
}{ IF "{ I }" = "C*" "C" ""
}
and so on until
}{ IF "{ I }" = "Z*" "Z" ""
}

Once you've created the pack of fields you need, you can save them to
an
Autotext (or another file) for re-use. I've used the wildcard "*"
which
matches multiple characters rather than "?" (matches one) or "."
simple
for
flexibility. If the Database field could contain lower case but you
actually
want uppercase in your letter, you can use
{ MERGEFIELD myinitialfield \*Upper }

in each comparison.

There are other possible approaches...

Peter Jamieson

"Chris Stammers" <ChrisStammers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:2CD4621A-E7DE-47FF-8DB1-F56125D06058@xxxxxxxxxxxxxxxx
Hello,

I have to prepare a letter and the client has requested that the
full
stop
after a person's initial be removed. This is in our mainframe
database
like
this and as such can't be removed from there. Is there a way to use
part
of a
field or tell it to remove punctuation? I am using word 2000.

Thanks,
Chris









.

Relevant Pages

  • Re: Email Merge in Word
    ... Peter - I was copying and pasting from your comments into sql. ... "Peter Jamieson" wrote: ... Let's try the other query now - I have modified it a bit: ... get an error message about the string 2048 characters so it doesn't work. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: field truncated
    ... > Peter Jamieson Wrote: ... Word is treating the result of the Database field as the ... My data source is an Oracle database. ... Can you post the SQL ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Email Merge in Word
    ... "Peter Jamieson" wrote: ... Let's try the other query now - I have modified it a bit: ... The SQL version: ... get an error message about the string 2048 characters so it doesn't work. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Email Merge in Word
    ... "Peter Jamieson" wrote: ... This above is what I have now and the query will not run. ... visual designer (at this point you may find it easier to work in the SQL ... get an error message about the string 2048 characters so it doesn't work. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Using part of a field
    ... If you can work out the necessary AS400 SQL code you can issue it in a VBA ... Another approach may be to use a DATABASE field. ... MERGEFIELD your_field_name}',1)"} ... sort of thing in a query. ...
    (microsoft.public.word.mailmerge.fields)