Table cell display differences between 97 and XP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JW - 19 Apr 2004 16:32 GMT

I've alreadsy posted this to the Access group, but am now
wondering if it is a Word XP problem.

the long explanation is below, but to summarise:

An existing 97 Access database has been upgraded to XP.
The database has a number of queries relating to money and
these are merged as invoices, daily reports etc..

However, after the conversion (upgrdae?!) one of the
fields from the access queries to calculate VAT will not
display in Word in the correct format ie £11.17 it prints
out as 11.1702127659574

Does anyone know of any differences in Word XP and 97 that
might explain this?

JW

Longer explanation to Access group:

A 97 access database which has a number of queries to show
how much of various amounts are VAT eg "Rate VAT: [Total
Rate]*17.5/117.5" worked perfectly well in Access 97 and
the results were merged to various Word documents as
invoices, daily reports etc. in the correct format and
decimal places eg £11.17

After upgrading the calculations still appear to be
working in the queries as the resulting tables correctly
display amounts as above ie £11.17

However, when these are now merged to Word XP they will
only display as 11.1702127659574 (!?)

If you ROUND the calculation in the query the resulting
table still displays accurately £11.17 but the resulting
merged document will only display as 11.17 ie without the
currency symbol (!!??)

Obviously it is possible in the Word document to add a £
symbol but ... this is going to be a lot of additional
work, ie all the existing queries with this calculation
will have to be ROUNDed and then all the existing merge
documents will have to have the new field names added and
the £ sysmbol added.

Why is the happening?

Why would it change between 97 and XP?

JW

Reply to this Message

Graham Mayor - 19 Apr 2004 16:41 GMT

Microsoft has changed the method used to connect to data and Word is not
required to provide the formatting. This is easily done with a switch - see
http://www.gmayor.com/formatting_word_fields.htm. If you want to revert to
the older connection method, this too is possible - see the Excel data
section (for which purpose Excel and Access have the same issues) of
http://www.gmayor.com/mail_merge_labels_with_word_xp.htm

Signature

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

Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

> I've alreadsy posted this to the Access group, but am now
> wondering if it is a Word XP problem.
[quoted text clipped - 48 lines]
>
> JW

Reply to this Message

JW - 19 Apr 2004 21:43 GMT

To Graham Mayor

Thanks for your reply

It and your very useful web page made sense on reading,
but ... I seem unable to achieve it in my document.

I must be typing something wrong in terms of spaces,
punctuation.

Is there any chance you could illustrate it for me with
one of my actual fields.

For instance the VAT calc field I need to change the
display of looks like this when I insert it:

«Rate_VAT»

but when I tried
{ = «Rate_VAT» \# "£#,##0.00;(£#,##0.00)"}
it doesn't work.

sorry if i am being a bit slow.

Many thanks

JW

PS why isn't it consistent in XP the fields calculated in
Access that are either + or - calc don't have display
problems - it only seems to be the calc fields with * or
\ - why have they done this?!!

>-----Original Message-----
>Microsoft has changed the method used to connect to data and Word is not
[quoted text clipped - 16 lines]
>> fields from the access queries to calculate VAT will not
>> display in Word in the correct format ie £11.17 it

prints

>> out as 11.1702127659574
>>
[quoted text clipped - 38 lines]
>
>.

Reply to this Message

Graham Mayor - 20 Apr 2004 10:16 GMT

You are correct, you have the syntax wrong. The easiest way to do this is to
use CTRL+F9 to enter the field boundaries. {} and then add the field
construction between them:-

{Mergefield Rate_VAT \# "?,0.00;(?,0.00)"}

An alternative is to put the cursor in the original field ?Rate_VAT? And
press SHIFT+F9 which will give you {Mergefield Rate_VAT} to which you can
add the switch in the brackets, before toggling the display again, then F9
to update.

Note also that you appear to be using smart quotes in the version you mailed
to me (please post here in future), instead of straight quotes.

The extra hashes are unnnecessary.

As for PS, I have no idea why Microsoft did this.

Signature

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

Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

> To Graham Mayor
>
[quoted text clipped - 100 lines]
>>
>> .

Reply to this Message

Graham Mayor - 20 Apr 2004 11:25 GMT

You are correct, you have the syntax wrong. The easiest way to do this is to
use CTRL+F9 to enter the field boundaries. {} and then add the field
construction between them:-

{Mergefield Rate_VAT \# "?,0.00;(?,0.00)"}

An alternative is to put the cursor in the original field ?Rate_VAT? And
press SHIFT+F9 which will give you {Mergefield Rate_VAT} to which you can
add the switch in the brackets, before toggling the display again, then F9
to update.

Note also that you appear to be using smart quotes in the version you mailed
to me, instead of straight quotes

The extra hashes are unnnecessary.

As for your last question, I have no idea why Microsoft did this.

Signature

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

Web site www.gmayor.com
Word MVP web site www.mvps.org/word
<>>< ><<> ><<> <>>< ><<> <>>< <>>< ><<>

> To Graham Mayor
>
[quoted text clipped - 100 lines]
>>
>> .

Reply to this Message