Mergefield formatting codes seem to combine digits in numeric data

[mdupris]

So I have a DOC file which mail merges from a SQL Server view. The
source data field is a SQL Server "money" type. It should display as a
dollar and cents value within Word. And it works fine except if there
is just a single non-zero digit to the right of the decimal point in
the raw data. If no formatting fields are supplied in the MergeField,
the data appears as 50.9, or 371.4, as I'd expect. However I've yet to
find a formatting for the mergefield which correctly dipslays the
missing trailing 0. Instead Word seems to combine digits. For instance
if the source data is 371.40, it displays as '375.00'; if the soruce is
50.9, it displays as 59.0 If the raw data has two digits to the right
of the decimal point however, e.g. 549.23, it displays correctly in
Word. I've tried innumerable varieties of format codes (\# $#,###.##,
\# $#,###.#0, \# $#,###.00, \# #,###,##0.00 \# $,0.00 \#$0.00 etc.
etc) with the same results each time. Anyone ever run into this before?
Even better, anyone know a solution? (Word 2003 / SP2 running on
Windows Server 2003, SP1; SQL Server 2000).

= Marchand =

 

[Graham Mayor]

Let me get this right?

If the data contains 371.4 then {Mergefield Fieldname} displays 371.4
but {Mergefield Fieldname \# "$,0.00"} displays 375 ?
I cannot see any mechanism whereby that should happen, however try two
things
1. {Mergefield Fieldname \# "$,0.00" \*Charformat}
2. {QUOTE{Mergefield Fieldname} \# "$,0.00"}
Don't forget to update the field after making changes (F9).

I take it your regional settings are English and that the default thousands
separator is ',' and decimal '.' ?

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


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

 

[macropod]

Hi,

Your description of '371.40' displaying as '375.00' fits a source that is
actually '371 40' or '371 4', and is reinforced by your description of
'50.9', displaying as '59.0'. Another possibility is your regional settings
having a space where the decimal point should be, but I would have expected
that to affect '549.23' also.

I suggest rechecking your data source and your regional settings - either
could be the culprit.

Cheers

 

[mdupris]

Thank you both for your replies. The regional settings on the server
are plain vanilla United States/ English with all the nromal currency
symbols, etc. Nothing special there. The source data really, truly is
the 50.9 and 371.4 in the source table in SQL Server. The values
perform as exepcted in all other respects within SQL Server and in
other extracts (e.g. into Excel).

Updating the mergefield after making the formatting changes also
doesn't have an impact, including with the two variations Graham
suggested.

I'm wondering if it's a quirk with a "money" datatype within SQL
Server. I haven't see any reports of this being a problem, but it is
the only thing even vageuly unusual about this sitaution. We have
dozens of other mail merge documents, all of which work well but no
other of which have 'money' field as a datasource. Does this make sense
at all?

 

[mdupris]

The solution/work-around was surprising. If I switched the data
interface to SQL server from ODBC to OLE DB, and made no changes to the
document itself, the formatting problem resolved nicely. 371.4 come out
as 371.40 as expected. I'll leave it to wiser minds than mine to decide
why a change in the data interface between Word and SQL Server would
make a difference in how mergefield formatting works. Me, I'm just
happy to be able to stop banging my head against the wall.

= Marchand =

 

[Peter Jamieson]

If the explanations given here didn't help and you are still looking for a
solution, the problem /may/ be to do with the money type. There is certainly
a similar problem with the "currency" type used in Access/Jet in some
circumstances.

Something worth trying is to use a cast or convert function in a SQL Server
view to change the data type, or if you aren't in a position to do that, you
may be able to issue the necessary Transact-SQL in the SQLStatement
parameter of a Word VBA OpenDataSource call (and there may be other ways to
do it).

Peter Jamieson