Bizarre mail merge error - help needed

[Andyatwork]

Dear all,

This is only tangentially related to excel so i hope this is the right forum. I am doing a mail merge, importing 3 fields; a single letter, a cash value and a numeral.

For some/most of the rows of data this is all boringly predictable and correct. For some rows of data, windows has apparantly chosen to add the individual numerals in the cash value together and import that value as the field.

ie, the merge source shows £8.900000 as the cash value, the merge document shows £17.00. £40.200000 in the source is coming across as £42.00.

I have tried reconnecting the merge source, rebooting the machine, all the obvious stuff but I cannot for the life of me figure out how or why word/excel is deciding to randomly bork the values it is importing.

Anyone seen this before? got any ideas How i can resolve this? Anything at all, I might need to call in mulder and scully because it is proper weird.

Many thanks,

Andy

 

[Macropod]

Hi Andyatwork,

You could try changing your data connection method to DDE. That usually preserves your data formats. Failing that, you can apply formatting switches to the mailmerge fields in Word. For example, to control number & currency formatting in Word:
. select the field;
. press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
. edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
. position the cursor anywhere in this field and press F9 to update it;
. run your mailmerge.

Note: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
. \# 0 for rounded whole numbers
. \# ,0 for rounded whole numbers with a thousands separator
. \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
. \# $,0 for rounded whole dollars with a thousands separator
. \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values


The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.

If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.


The problem you're having with the merge apparently adding the individual numerals in the currency field together suggests there's something odd about the way that field is formatted in Excel. For example, it could happen if the Excel cells used spaces as a thousands separator or something other than a (true) period as the decimal separator.

 

[Andyatwork]

Hi Macropod and thank you for the response,

Yep, have tried a variety of switches for the decimal place precision and for the lines that are ok, it is working perfectly. All the merge field columns in the source are formatted the same way but some are failing which makes me think it is not the source formatting that is at issue. For it to add the individual digits and inport the sum is so odd, never seen it before. Also appears to only effect certain specific values; i.e., whenever the target value was £8.90 it would import as £17.00.

Bodged a fix by copy all and paste into a new document which suggests the word tempalte had got corrupted somehow. There has been a bit of a spate of that at work (corrupted merge templates doing weird things) so am wondering of the jolly funsters in IT have been running updates or something.

ah well, guess i'll chalk it up to a Windows "feature".

 

[jimrward]

what version of MSWORD and EXCEL are you using ??

there is a little known bug with Office2000, where if you have any non standard characters in your merge data it does weird things, I had the problem with single quotes and dowble quotes where it was used for feet and inches, once I had tracked it down I used plain text and numbers

do you use the £ pound sign in your data, if so try editing a sample of the data and remove it to leave purely numbers and see how you get on

 

[Andyatwork]

It's and Excel '03 file (.xls) in Excel '07, merging into Word '03 (SP3).
The affected fields do not contain any non alpha-numeric characters.

A colleague has a had a similar issue with dates. A merge source is maintained and updated frequently with new lines of data and letters generated for the new lines. The merge source will say one thing, in the same format that has been working perfectly fine for the last umpteen months and for no good reason decides to throw a wobbler, with the word document displaying something different.
e.g., merge source says "18/05/2010", merged doc says "25/05/2010".

 

[jimrward]

do any of the other surrounding fields in the data source have any characters in other than letters and numbers, it only needs a single double quote for instance to throw the whole thing out, the problem may still exist in the DDE linkage between office products