Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
puzzling number format problem in conditional field
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. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
puzzling number format problem in conditional field
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 -- macropod [MVP - Microsoft Word] ------------------------- "Jesse" wrote in message ... 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. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
puzzling number format problem in conditional field
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 My web site www.gmayor.com Word MVP web site http://word.mvps.org Jesse wrote: 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. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
puzzling number format problem in conditional field
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 "Jesse" wrote in message ... 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. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
puzzling number format problem in conditional field
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. -- Peter Jamieson http://tips.pjmsn.me.uk "Jesse" wrote in message ... 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. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
puzzling number format problem in conditional field
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Field codes: MergeField field RTF format problem | Mailmerge | |||
Conditional Currency/Text Field Format in Merge | Mailmerge | |||
Number format field | Page Layout | |||
How do i format how a number field displays? | Mailmerge | |||
Attaching Expansion Pack to Word Documents - Puzzling Problem | Microsoft Word Help |