Using a bookmark and applying a switch to it

[Mark]

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.

 

[macropod]

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

 

[Mark]

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,

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

--
macropod
[MVP - Microsoft Word]

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.

 

[Peter Jamieson]

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

 

[Doug Robbins - Word MVP]

It would be a simple task to create a macro that iterated through the cells
of the table that contain the numbers and format them the way you wish.

Is it the only table in the document, if not, which one? What column in the
tables are the value in? Does the table have a header row?

The following code will apply currency formatting to the numbers in the
third column a the first table in a document, starting with the number in
the second row

Dim i As Long
Dim number As Range
With ActiveDocument.Tables(1)
For i = 2 To .Rows.Count
Set number = .Cell(i, 3).Range
number.End = number.End - 1
number.Text = Format(number.Text, "$#,###.00")
Next i
End With


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

 

[Peter Jamieson]

None of the other things I suggested reallly has a hope of being useful in
the environment you describe, so I'd start with Doug's suggestion and take
it from there.

Peter Jamieson

 

[Mark]

Hi Doug, Peter and Macropod,

Doug, your suggested Macro works a treat! It is the immediate solution that
I'll use although I would prefer the vendors to provide some more flexibility
as it is only applicable to this one template and we use up to 20 - may make
for an interesting Toolbar!

Thanks for all your assistance.

Mark