MERGE Codes and formatting pulled in data problem

[surfernat]

I really cant explain this its very odd. Everything works except one field. To explain the problem I need to explain the overall setup so please bare with me.

DATASOURCE
The data source is an Excel 2003 spreadsheet. It is used to record outstanding balances. I have four columns 0-30days, 30-60days, 60 -90 days and older. These columns show the age of each outstanding balance. In the same spreadsheet I have a field that calculates which letter they are to recieve. ie 0-30 letter a, 30-60 letter b, etc etc

WORD 2003 DOCUMENT
I have created 4 letters Latter a, letter b, letter c and letter d.

When you open letter d a query runs which selects data from the excel sheet and populates the relevant fields in the document.

In each letter I have the same table showing outstanding balances. ie

0-30 30-60 60-90 Older
£- £- £200.00 £-

If the client has an oustanding balance no older the 30 days then the total will display in the 0-30 column, if he hasnt then the field should automatically display £- mainly to keep it neat looking. This works really well and I using the following syntax to display the described results

{MERGEFIELD"0-30" \#"£#,##0.00;(£#,##0.00);"}

Now all works fine except 60-90 it has the exact same syntax however the results are not formatted as specified, the brought in figure is left unformatted instead of being £200.00 its just 200 and when no figure is brought in its left blank instead of £-. I have copied the 0-30 syntax and pasted it in the 60-90 and then amended the mergefield but the results are same.

I have checked the source data and its formatted as £ with two decimal points. It just doesnt work for 60-90.

Does anyone have any ideas what else I can try please. Its really annoying me!! Thanks

 

[SkipVought]

Hi,

Your SOURCE DATA in Excel may be the problem.

Check to make sure that you have no leading or trailing SPACES or any other non-numeric character in the cell in question.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[surfernat]

WOW that was quick! Thanks Skip I was guessing the source data was the problem somewhere along the line. I will check and report back.

Thanks again for taking the time to read!

 

[surfernat]

hmm source data seems fine!!!
Nothing dodgy in any columns no spaces. Really odd!!
I still agree its something to do with the source data but cant find it anywhere!!

 

[SkipVought]

Exactly HOW did you go about determining that the data was OK?

I'd suggest this process:

1. enter the value 1 in an unused cell.

2. copy that cell

3. select the DATA in the column(s) containing this number data.

4. Edit > Paste Special -- MULTIPLY

5. delete the value 1

6. SAVE

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[surfernat]

Skip thanks for staying tuned

The data is pulled from one sheet in excel and exported via macro to a secondary filtered sheet in the same workbook. The 4 columns in question appear to have the same formatting. There is approximately 40 rows in total nothing too arduace to scan through. I have entered each cell and checked its formatting and its formulation and all seems okay. Each cell is however formatted using a custom style £* #,##0.00;(£* #,##0.00);£*"-"_ I will however attempt your suggestion. Thanks again

 

[SkipVought]

The FORMATTING on your data source means absolutely NOTHING.

The vital information is the underlying value, which is the ONLY information passed in MailMerge.

You can vies the underlying value by changing the Cell Format to GENERAL and checking to see that you only have a pure numeric value, and NOTHING ELSE. Then you can return the custom format if you wish, but it, in reality, has no relevance!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]