Using part of a field

[Chris Stammers]

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

 

[Peter Jamieson]

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]

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]

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]

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]

{ 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]

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

Regards,
Chris

{ 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

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.

 

[Doug Robbins - Word MVP]

Another way to do this would be to use a Wildcard Find and Replace in Word
to look for individual upper case characters that are followed by a period
and then delete the period.

See the article "Finding and replacing characters using wildcards" at:

http://www.word.mvps.org/FAQs/General/UsingWildcards.htm

This could be done either after the merge operation or you could have an
intermediate step to create a catalog merge, then do the replace on that and
use it as the data source for you final merge operation. Either way, the
process could be pretty much automated.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

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

Regards,
Chris

{ 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

message

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.



:

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

message
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

:

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

message
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