Re: Using part of a field

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: Using part of a field
    ... I'm not familiar with MS Query or Word VBA. ... the database, you ought to be able to modify the SQL that MS Query generates ... as long as the SQL dialect being used can do it. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: complex filter and calculations in access
    ... switch to SQL view. ... query by switching to datasheet view, ... of your database using the from address in this post. ... pre-op infections yes/no ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ... for a s/w developer and a s/w developer simplifies for the end user). ...
    (comp.databases.pick)
  • Re: complex filter and calculations in access
    ... when using a subquery on the same table as the main query, ... switch to SQL view. ... of your database using the from address in this post. ...
    (microsoft.public.access.queries)
  • Re: A little more meat this week
    ... said data includes lists." ... implementation with a query processor capable of returning a result set ... then we can call it a result bag (SQL ... I'm looking at the data model and not database tools at this ...
    (comp.databases.pick)
Loading