Merged word-doc displays false data/wrong format 2

[dune2]

My office suite tends to act up occasionally (work-computer), and especially my mail-merge feature drives me up the wall. When I merge numerical fields, bizarre things happen:

1. regardless of how the numbers are formatted, they are only entered into the word-doc as a simple number. E.g. the field in Excel reads £30,000.00, the word doc only shows 30000.

2. Apparently randomly, but usually confined to certain columns, the numbers will be not exact but very long fractions. For example: a field has the exact number of 3, the merged document would show a 2999999999999999999999999997. A field of 3.5% (again, manul input, exactly 3.5) would show as:
3.5000000000000000000003E-2.

3. Dates are formatted mm/dd/yyyy, regardless how thety are formatted. This may be solved by the answer to this threat:
thread68-1249556

Please note that my computer is the only one showing this problem, so a merge on a different PC doesn't show this issue.
It drives me nuts though, so I'd really appreciate any possible help!

Cheers!

 

[SDPT]

I wuld verufy the check the document settings before the merge, I would gues that one it English US and the other ENG UK...

_________________________________
MCP

 

[dune2]

But would only affect the dates, right? How about the weird number I am getting?

 

[macropod]

Hi dune2,

You can preserve the original data format by connecting to your mailmerge source via DDE instead of OLDB. DDE uses shared memory to exchange the data. If you use other data transfer methods, the data are transferred in the native format in which the source application stores it rather than with the formatting that you applied there.

To choose the DDE data transfer method when you're connecting to an Excel worksheet during a mail merge, all you have to do is select one check box in Word:
1. On the Word Tools menu, click Options, and then click the General tab.
2. Click Confirm conversion at Open.
At the point in the mail-merge process where you connect to your data file, after you locate the file you want to connect to, the Confirm Data Source dialog box opens. Click 'Application via DDE (*.???)', then 'OK'. In the Application dialog box, select the table/range containing the information you want to merge, and then click 'OK'. The data will now be formatted the same in your merged documents as they look in the source file.

You should turn off the Confirm conversion at Open option after you finish your mail merge.

Alternatively, you can add formatting switches to the mailmerge fields in Word, making their formatting independent of the data source (which is what you now have). For example, select a given mailmerge field and press Shift-F9, then:
. for currency, add '\# £,0.00' to field, so that you get something along the lines of {MAILMERGE xlAmount \# £,0.00}.
. for numbers generally, add '\# ,0.00' to field, with as many 0s after the decimal point you want for precision and as many 0s before as you want for leading 0s, so that you get something along the lines of {MAILMERGE xlValue \# ,0.00}
. For dates, there are many possibilities, for displaying the result as dd/MM/yy, d MMM yyyy, yyyyMMdd and so on. Simply choose your format. One possibility is {MAILMERGE xlDate \@ "dddd, d MMMM yy"} will give you a result like 'Tuesday, 5 March 2007'. Note months must be specified as 'M's not 'm's - which are for minutes.
With the above examples, use your real data names, instead of the xlAmount, xlValue and xlDate that I've used. And if your mergefields have \* MERGEFORMAT switches, delete them.

Cheers

[MS MVP - Word]

 

[dune2]

Thanks Macropod, I will try that!