Using a bookmark and applying a switch to it

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark - 07 Dec 2006 06:33 GMT

Hi.

I have an external application which merges data from an Oracle DB. We need
to perform a switch on the data to reflect currency however, the available /#
switches do not apply and, instead, the field is then returned blank.
Selecting F9 does not show the Mergefield codings.

What I am reduced to understanding is that the mergefields are as is stored
in the DB and cannot be represented.

I saw a reference some time back to a process whereby the mergefield is
brought into another section of the document and Bookmarked. Then, I call the
bookmark and apply the currency formatting to it instead of the mergefield.
Can anyone refer me to an article which could explain how to do this or
perhaps, refer me to a sample VBA macro which can assist here.

Thanks in advance.

Reply to this Message

macropod - 07 Dec 2006 07:30 GMT

Hi Mark,

Your document description suggests it's the post-merge file, rather than the
main merge document. The merge process produces an output file in which the
mailmerge fields are replaced with the relevant values. Any field
modifications, therefore, need to be made in the main merge document.

I note that your refer to "the available /# switches". The correct syntax
for a numeric picture switch is a variant of '\# $,0.00'.

Cheers

Signature

macropod
[MVP - Microsoft Word]

> Hi.
>
[quoted text clipped - 13 lines]
>
> Thanks in advance.

Reply to this Message

Mark - 07 Dec 2006 10:00 GMT

thanks for your response.

I agree with your assessment as a post-merge file and I have tried to edit
the main-merge document but ot no avail. Something is happening in the middle
which makes the switching difficult.

I have set the switch as \# (just my Unix brain interfering for a moment!!)
but still, these didn't work which lead me to the thought of using the values
as bookmarks and formatting them or writing a VBA macro to do same. For what
it is worth, the application uses generic numeric fields in the underlying DB
as it is used for multi-currency and the vendors are of no assistance to how
to get around this limitation.

The fields are in a table with regular mergefield formatting and it is
Office 2000.

Thanks again.

> Hi Mark,
>
[quoted text clipped - 30 lines]
> >
> > Thanks in advance.

Reply to this Message

Peter Jamieson - 07 Dec 2006 10:37 GMT

The following kinds of manoevre have been known to help:

{ SET mylocalnumber { MERGEFIELD mynumber } }
{ REF mylocalnumber \#0.00 }
{ QUOTE { MERGEFIELD mynumber } \#0.00 }
{ SET mylocalnumber { QUOTE { MERGEFIELD mynumber } } }

(All the "{}" pairs have to be the special field code braces you can insert
using ctrl-F9)

However, Word does sometimes have problems with certain types of numeric
data - e.g. "currency" type fields in Access databases have not always
behaved as you might hope. This could be another of those situations. If you
insert a plain { MERGEFIELD mynumber } field, what do you see?

Unfortunately, I don't currently have an Oracle DBMS to test things with,
but assuming you are getting your data "directly" from your Oracle database
(i.e., in Word 2000, via an Oracle ODBC driver), there are other things you
could try, e.g.
a. there may still be two Oracle ODBC drivers - one provided by Microsoft,
which takes you up to about Oracle 6 in compatibility terms but AFAIK can
read compatible type fields in later Oracle versions, and one provided by
Oracle (there are other third party drivers as well). Perhaps worth trying
both.
b. Also worth exploring any options in the ODBC driver configuration.
c. Using Word VBA and the OpenDataSource method you can issue SQL that may
be able to transform these numeric fields into something that Word can
handle. However, the length of the SQL statement is fairly limited (255 or
511 bytes or characters) and if you need to transform a lot of these numbers
the SQL may become too long.

This assumes that you cannot create your own views in Oracle to give you
exactly what you need.

Going a bit further, other potential solutions include:
a. getting the Oracle data via another route (e.g. a Jet linked table, or
via a csv format file)
b. using Word VBA and the Mailmerge objects to preprocess the data for each
record before it is merged
c. using Word VBA to do one merge per record and preprocess the data for
each record before it is merged.

Let's hope one of the early suggestions (or one of macropod's) does the
trick. otherwise, you will find macros that can do (c) if you search this
group using Google Groups, or I can post something here.

Peter Jamieson

> Hi.
>
[quoted text clipped - 18 lines]
>
> Thanks in advance.

Reply to this Message