How do I format a mergefield?

[Mags]

I have a table with a series of merge fields in a column.
I want to force the merge fields to always have 2 decimal places.
I'm using Word 2003

 

[Graham Mayor]

Use a number switch eg {mergefield fieldname \# "$,0.00"}
See http://www.gmayor.com/formatting_word_fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[MrMellie]

I have a merge document (well, several actually) where the formatting is
playing up. The mailmerge data is stored in a VFP dbf with a value field,
width 10, and 2 decimal places.

On the merge document if no formatting is applied 529.20 will appear as 529 2.
However, using {mergefield LOCALVAL \# £,0.00} gives £531.00 and the value
is rounded up.

I've tried various combinations like {mergefield LOCALVAL \# £,#.00} (same
result), {mergefield LOCALVAL \# £,#.##} (gives £531.).

The original formatting that used to work in Word 97 was {mergefield
LOCALVAL \# £#,##0.00} but since moving up to Word 2008 everything has
broken! Any help would be appreciated.

 

[Doug Robbins - Word MVP]

In Word 97, the connection to the data source was probably by DDE. Did you
mean Word 2007 or 2008 (which maybe a Mac version) If you meant Word 2007,
click on the Office button and then on Word Options and go to the Advance
item and scroll down to the General section and check the box against
"Confirm file format conversion at open" Then after doing that, re-attach
the datasource and in the dialog box that appears, try connecting via DDE
(It may be necessary to click on the "Show all connections" button to see
all of the available options.

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

My guess is that you are getting your data from FoxPro using ODBC, in which
case the problem you describe does occur. In essence when a mergefield
contains more than 1 number separated by spaces, and you add a numeric
format switch, Word adds the numbers together.

So if localval was 123.9, which comes through to word as "123 9", {
MERGEFIELD localval \#0 } would result in 132.

If you are using quite old format .dbf tables, you may be able to get your
daya via Jet, which may return the data correctly.

otherwise, the simplest fix is likely to be to get the data via OLE DB, but
whether or not that works may depend on the version of FoxPro used to create
your files. To use OLE DB, you first need to download the OLE DB provider
from the MS download site, install it (and you may or may not agree with the
licensing terms). Then, when you connect to your data source, in the Select
Data Source dialog box, click New Source, select the Other/Advanced optin,
select the Microsoft OLE Db Provider for Visual FoxPro, and go through the
dialogs. Eventually you get to connect to a table in your data source. Word
saves the connection information in a .odc file, then you connect in future
via that.

And I say simplest because the alternatives are probably significantly more
complicated.

e.g., you can connect using ODBC via MS Query, and specify a SQL query that
converts your number to text (which, paradoxically perhaps, Word will then
understand), e.g. if you have a single number mynumber, you can use

SELECT str(mynumber,10,2) as mynumber FROM mytable

Or you could even engineer to calculate the result using Word fields, but
it's probably best not to go there unless all else fails.

Difficult to tell which way would be best for your situation, but perhaps
worth trying the OLE DB approach anyway.

 

[MrMellie]

Doug, sorry, yes I meant Word 2007. My fingers got excited at the prospect of
using an up-to-date version of Office rather than the 10yr old version we'd
been stuck with for years! lol

Peter, you are correct in that the datasource comes via ODBC. It uses the
"FoxPro Files - Word" User DSN. The table was created in VFP 7 as a free
table.

I'll give the OLE DB route a go as we have those drivers already installed
on our PCs. Thanks for the help, I'll let you know how I get on.

 

[MrMellie]

Peter, many thanks for your advice! Changing the data provider to OLE DB has
worked.

 

[Peter Jamieson]

Thanks for the useful confirmation!