puzzling number format problem in conditional field

[Jesse]

I am doing a Word mailmerge with an Access database -- Office 2000.
The problem has to do with currency fields in the database and getting
the \# formatting to work.

Based on what I see, Word has default formatting for currency numbers
- putting a dollar sign in front of them unless you tell Word
otherwise. I have been able to override this by using a format
switch. So:

{MERGEFIELD ttl_price \###.00}

However, when I add this switch to this mergefield within a
conditional statement -

{IF { MERGEFIELD country} = "United States", "{MERGEFIELD ttl_price
\###.00}" "" }

- the formatting code disappears when I switch to "View merged data"
and back - and so it has not effect.

A workaround has been to add the mergefield to zero, and then the
formatting will stick and work:

{ = {MERGEFIELD ttl_price} + 0 \# ##.00 }

However, this causes a troublesome blank space to appear before the
number.

Another workaround, which is the most puzzling to me (as to why it
works): I create {MERGEFIELD ttl_price \###.00} outside the IF
statement and then paste it inside the statement,. Even with this
approach, every mergefield that I want within the IF statement has to
be pasted in as a block; if I just past in 1 field after others are
there, the new 1 filed disappears.

Please note, all of the { } brackets are created by doing an insert -
i.e., not by typing the brackets. AFAIK, when adding a mergefield one
has to manually add in the formatting - i.e., there is no "insert
field switch format picture", so entering it by typing should work.

Also, in order to try to suppress Word adding a $, I did try changing
the field type in Access from currency to number, but then anything
right of the decimal was dropped.

Any ideas as to why there is a problem formatting within the IF
statement? Especially, why creating the code outside the IF statement
and then pasting it in works? Thanks. -- Jesse.

 

[macropod]

Hi Jesse,

You should be able to achieve what you want with:
{IF{MERGEFIELD country}= "United States" {MERGEFIELD ttl_price \# 0.00}}
or, if as I suspect, your $ sign is causing the data to be treated as a text string:
{IF{MERGEFIELD country}= "United States" {={MERGEFIELD ttl_price} \# 0.00}}

If you put a comma in front of the leading 0 in the switch, Word will add a thousands separator for you as appropriate.

Cheers

 

[Graham Mayor]

Assuming comm is your regional thousands separator, you should do better
with

{MERGEFIELD ttl_price \# ",0.00"}

and

{IF { MERGEFIELD country} = "United States" "{MERGEFIELD ttl_price \#
"0.00}" }

See http://www.gmayor.com/formatting_word_fields.htm

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Doug Robbins - Word MVP]

Word does not have any default formatting for currency numbers.

See "Formatting Word fields with switches" on fellow MVP Graham Mayor's
website at

http://www.gmayor.com/formatting_word_fields.htm

Your field construction of

{IF { MERGEFIELD country} = "United States", "{MERGEFIELD ttl_price
\###.00}" "" }

should be

{IF { MERGEFIELD country} = "United States", "{MERGEFIELD ttl_price \#
",#.00"}" "" }

assuming that you do not want the $ sign to appear.

If after making that correction, the fields still disappear, it is probably
because they need updating for the effect of the If...then...Else
construction to be displayed.

With the above construction, you will not of course get any result if the
country is not the USA.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

Since everyone else has already covered most if not all of the ground, I
hope this won't confuse any further, but:

Based on what I see, Word has default formatting for currency numbers
- putting a dollar sign in front of them unless you tell Word
otherwise. I have been able to override this by using a format
switch. So:

FWIW, this is how it looks in Word 2000 because Word gets the data from
Access using DDE by default, and with DDE you tend to get the formatting as
well as the "underlying data" - in this case, just a number. But in fact, as
far as Word is concerned, the value coming in from Access is just a piece of
text - it's only when you try to do a calculation on it or format it as a
number that Word tries to extract the numeric part of the text. At least,
that's what I usually assume is happening. If you are not seeing the numeric
switch applied, it's probably because - as Doug suggests - the fields
haven't yet updated.

{ = {MERGEFIELD ttl_price} + 0 \# ##.00 }

If you have to use an = field, the only problem is that you actually have a
space in that switch, i.e. you need

{ = {MERGEFIELD ttl_price} + 0 \###.00 }

One reason field codes sometimes disappear when you copy them into an IF
field is that the field code has not been evaluated yet and has no result.
Typically, if it has a value, you may see the value or the preview value. It
may also depend on whether the entire document has "view field codes"
switched on at that moment, or whether just the field you are inserting into
is displayed as a field code (e.g. because you entered it manually when
evrything else is displaying preview results or the <<fieldname>> format, or
because you used the right-click menu to view the field codes in the current
selection). Typically, using Alt-F9 again will show all the field codes
anyway, unless they are formatted as hidden text and you don't have the show
hidden option turned on.

there is no "insert
field switch format picture", so entering it by typing should work.

In theory you can do this by using the Insert|Field menu and clicking the
field Codes button so you can type in anything you like. But IMO it's easier
to use ctrl-F9 and then type what you want, as you are doing.

Finally, I have once or twice seen people say that the result they got when
they used the "Insert Word Field|If...Then...Else..." method to insert a
nested field was different from the result when they did it all manually,
but I have never been able to work out whether that is because of
a. something the user has done differently,
b. perhaps something such as "when they do it one way, Word evaluates
something that it doesn't evaluate when it does things the other way",
c. or Word does something more subtly and invisibly different in the two
cases.

I suspect if anything it's probably (b), otherwise I think there would be a
whole lot more problems getting IF fields to work than we encounter.

Finally, there can be problems using currency type fields when you connect
using ODBC/OLEDB - probably not something to be conscerned about now, but
perhaps to bear in mind should you need to switch the connection method for
some reason or upgrade to a later version of Word where OLE DB connections
are the default.

 

[Jesse]

Thank you all for the help. Among other things, I learned about using
a comma in the switch. And, I now see that I was missing quote marks
around the format "picture", though formatting still works without the
quotes - sometimes.

And, http://www.gmayor.com/formatting_word_fields.htm is great.

With some more testing, I found that the "problem" - what causes the
"default" $ sign usage in the mail merge - comes from the data type in
the Access table. The data type is carried over, but the data field
properties are not. So a field that is set in Access to currency type
and to show there w/o $ is received by Word merely as a currency
number -- and so assumes that $ should be added. And, an Access
"number" type that has ".00" is truncated (by default) in Word -- so
"15.00" becomes "15" while "14.95" yields "14.95".

Peter, thanks for the a-b-c anecdotes -- there seems to be something
going on that may just be unknowable. And, it is good for me to see
that I am not the only one who writes in one message two paragraphs
that begin, "Finally..."

Again, thanks all. -- Jesse