Sum up field codes not working right

Question

I have values brought into a Word from another software in this format:

example 1: 2 000,00 $ example 2: 50,00 $ example 3: 211,50 $

Since Word does not allow to do maths on numbers of that format, I have to transform the numbers into a period format like, for example 1, 2000.00 or example 3, 211.50

The way I do so is:

{ REF DataName \# "0.00" }

For example 1 and 2 above, it works. However, when the number as a decimal, like example 3, the output is 261.00, which is not right.

Looking forward for help with this, as I've tested multiple methods, like { =DataName } or even SET commands.

Answer 1

If VBA (or some other programming approach) is an option then I think that is the best and most versatile way to solve this.

There is an approach that uses a DATABASE field to reformat the numbers. It can usually be made to work on a Windows desktop PC installation of Office, but it may not work in your Citrix environment because

1. it needs to be able to access an external file, which I would guess in a Citrix environment would be on a server somewhere. More details below.

2. it uses the Jet/ACE OLE DB provider - normally that would all be available on a desktop environment, but again I don't know whether Word would be able to do this in a Citrix environment.

Finally, if these numbers can come in with all sorts of different national numbering formats, I don't think it would be easy to write fields (and possibly not even the VBA code) that would cope with all of them.

So I'm assuming the numbers are coming in as you mentioned and that you need to end up with something that has no spaces or "$", and "." as the decimal point. (Once you have got that, you can use Word's numeric formatting to format them).

If your numbers have any quotation marks in them, this DATABASE field technique probably won't work. For example, I think the Swiss may use a single quotation character "'" as the thousands group separator.

If the user has access to a file system via, say a "g:" drive or via, say, \myserver\myshare then what you need is a small plain text file in a known place in that file system. For example, suppose you have a file called

g:\standard\i.udl

or

\\myserver\myshare\standard\i.udl

that contains the following text:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.;Jet OLEDB:Engine Type=96;

If you have an older version of Word, you need to specify the older Jet provider, using e.g.

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.;Jet OLEDB:Engine Type=96;

A .udl file is a "Universal Data Link" file that allowed you to specify an ODBC or OLEDB data source. Word still recognises them, and using this file allows you to issue Jet/ACE SQL queries without actually having a .mdb or .accdb database.

If the user system can only access files via HTTP, instead of a .udl file you will need a minimal Access .mdb file. You can create one in Access. It doesn't need to have any user tables or queries. Let's say it is at

http://192.168.200.200/standard/i.mdb

(I have only been able to test using http: here, not https:, so I am not certain that this can be made to work with https:)

Now ensure that you can execute a Jet/ACE query by inserting the following example DATABASE field in your document (use ctrl-F9 for all the special field code braces and type everything else between them):

{ DATABASE \d "g:\\standard\\i.udl" \s "SELECT 1" }

(and the following will probably also work:

{ DATABASE \d "g:/standard/i.udl" \s "SELECT 1" }

)

or

{ DATABASE \d "\\\\myserver\\myshare\\standard\\i.udl" \s "SELECT 2" }

or

{ DATABASE \d "http://192.168.200.200/standard/i.mdb" \s "SELECT 3" }

depending on where you put your file. Ensure that backslashes in path literals are doubled up, as I have shown.

Select that field, update it via F9, and look at the result. If there is an error message and your path/access permissions etc. are all OK, then this approach is not going to work. If you see the result of the query (a couple of paragraphs with 1, 2, or 3 in the second one, then so far so good.

At this point I should point out that you can't put a DATABASE field in a Word table. If you need these values to be in a table you will have to put these DATABASE fields outside the table, then use a SET field to assign the result to a bookmark, which you can then { REF } inside your table.

What you then need is to verify that you can transform your number to a format that you can work with. I'm going to step through this, stating with your first example number

2 000,00 $

First change your field so it looks like this, where is whatever path worked above.

{ DATABASE \d "<thatpath>" \s "SELECT replace('2 000,00 $',' ','')" }

Ensure that '' is two single vertical quotation marks, not a " double quotation mark character.

Update that, and the result should contain the number without the spaces:

2000,00$

Now try

{ DATABASE \d "<thatpath>" \s "SELECT replace(replace('2 000,00 $',' ',''),'$','')" }

that should remove the "$"

and finally

{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('2 000,00 $',' ',''),'$',''),',','.')" }

which should give you

2000.00

But actually, your number is in a bookmark called DataName, so you really need

{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ REF DataName }',' ',''),'$',''),',','.')" }

where the { } are the proper field code braces you can enter with ctrl-F9.

(It's usually better to omit the REF and just use

{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }

If you need to substitute other characters, you can add more replace functions, but there is a limit (probably 255 characters) to the total length of the SELECT statement.

If that all works OK, the next step is to remove the extra paragraph mark that Word started inserting after Word 2000. Luckily, you can do that by nesting all that inside a QUOTE field, e.g.

{ QUOTE "{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }" }

You can also add a numeric format to the QUOTE field if you want, e.g.

{ QUOTE "{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }" \#"$,0.00"}

If you need to reference that value elsewhere (e.g. inside a table, then you can do

{ SET  mynumber "{ QUOTE "{ DATABASE \d "<thatpath>" \s "SELECT replace(replace(replace('{ DataName }',' ',''),'$',''),',','.')" }" }" }

then e.g.

{ mynumber \#"$,0.00 }

If there are a lot of these numbers in the document, there will be a lot of DATABASE fields, and they may take some time to execute. As far as I know, Word caches the database/.udl data so execution will probably speed up after the first DATABASE field is updated, but you should verify that Word doesn't actually hang, or appear to hang.

Again, if there are a lot of numbers to format, you can put all "common text" into other bookmarks which will help make these fields shorter and should improve maintainability. e.g. you could do

(at the beginning of the document)

{ SET dbpath "g:\standard\ia.udl" } (single backslashes here will work OK later)
{ SET qhead "SELECT replace(replace(replace('" }
{ SET qtail  "',' ',''),'$',''),',','.')" }
{ SET format "$,0.00" }

then use

{ QUOTE "{ DATABASE \d "{ dbpath }" \s "{ qhead }{ DataName }{ qtail }" }" \#"{ format }" }

Some people prefer to remove inessential white space and quotation marks. Personally I prefer to leave that stuff and would need to test to be sure what exactly can be omitted, but for example you can certainly reduce this to

{QUOTE "{DATABASE \d "{dbpath}" \s "{qhead}{DataName}{qtail}"}" \#"{format}"}