Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
I am attempting to determine the format code i need to enter in a numeric
form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Run a macro containing the following code on exit from the formfield (Change
the Text1 to the bookmark name assigned to the formfield) Dim IntPart as String, DecPart as String With ActiveDocument.FormFields("Text1") If IsNumeric(.result) Then If InStr(.result, ".") 0 Then IntPart = Left(.result, InStr(.result, ".") - 1) DecPart = Mid(.result, InStr(.result, ".")) If Len(DecPart) = 2 Then DecPart = DecPart & "0" End If Else IntPart = .result DecPart = ".00" End If Select Case Len(IntPart) Case Is 4 IntPart = IntPart Case 4 IntPart = Left(IntPart, 1) & " " & Mid(IntPart, 2) Case 5 IntPart = Left(IntPart, 2) & " " & Mid(IntPart, 3) Case 6 IntPart = Left(IntPart, 3) & " " & Mid(IntPart, 4) Case 7 IntPart = Left(IntPart, 1) & " " & Mid(IntPart, 2, 3) & " " & Mid(IntPart, 5) Case 8 IntPart = Left(IntPart, 2) & " " & Mid(IntPart, 3, 3) & " " & Mid(IntPart, 6) Case 9 IntPart = Left(IntPart, 3) & " " & Mid(IntPart, 4, 3) & " " & Mid(IntPart, 7) End Select .result = IntPart & DecPart Else MsgBox "You have not entered a number" End If End With From the above you should be able to deduce the results to be used for cases where the length of the integer part of the number is greater than 9 digits. -- 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, originally posted via msnews.microsoft.com "Owen" wrote in message ... I am attempting to determine the format code i need to enter in a numeric form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Hi Owen,
If you make the formfield type numeric, you can specify the number format as: ### ### ##0.00 -- Cheers macropod [Microsoft MVP - Word] "Owen" wrote in message ... I am attempting to determine the format code i need to enter in a numeric form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
You could do that but you are reliant on the user entering the decimal. Try
it with 12345 -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Owen, If you make the formfield type numeric, you can specify the number format as: ### ### ##0.00 "Owen" wrote in message ... I am attempting to determine the format code i need to enter in a numeric form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Doug
That is brilliant, not sure i'll ever grasp the Visual Basic concept like you guys on here, but i'll keep having a crack. Thanks also to Macropod and Graham for your replies. "Doug Robbins - Word MVP" wrote: Run a macro containing the following code on exit from the formfield (Change the Text1 to the bookmark name assigned to the formfield) Dim IntPart as String, DecPart as String With ActiveDocument.FormFields("Text1") If IsNumeric(.result) Then If InStr(.result, ".") 0 Then IntPart = Left(.result, InStr(.result, ".") - 1) DecPart = Mid(.result, InStr(.result, ".")) If Len(DecPart) = 2 Then DecPart = DecPart & "0" End If Else IntPart = .result DecPart = ".00" End If Select Case Len(IntPart) Case Is 4 IntPart = IntPart Case 4 IntPart = Left(IntPart, 1) & " " & Mid(IntPart, 2) Case 5 IntPart = Left(IntPart, 2) & " " & Mid(IntPart, 3) Case 6 IntPart = Left(IntPart, 3) & " " & Mid(IntPart, 4) Case 7 IntPart = Left(IntPart, 1) & " " & Mid(IntPart, 2, 3) & " " & Mid(IntPart, 5) Case 8 IntPart = Left(IntPart, 2) & " " & Mid(IntPart, 3, 3) & " " & Mid(IntPart, 6) Case 9 IntPart = Left(IntPart, 3) & " " & Mid(IntPart, 4, 3) & " " & Mid(IntPart, 7) End Select .result = IntPart & DecPart Else MsgBox "You have not entered a number" End If End With From the above you should be able to deduce the results to be used for cases where the length of the integer part of the number is greater than 9 digits. -- 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, originally posted via msnews.microsoft.com "Owen" wrote in message ... I am attempting to determine the format code i need to enter in a numeric form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Hi Graham,
Works for me - 12345 gets displayed as 12 345.00, which accords with the OP's requirement (I always require two decimal places, even if the user only inputs the integer value). -- Cheers macropod [Microsoft MVP - Word] "Graham Mayor" wrote in message ... You could do that but you are reliant on the user entering the decimal. Try it with 12345 -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Owen, If you make the formfield type numeric, you can specify the number format as: ### ### ##0.00 "Owen" wrote in message ... I am attempting to determine the format code i need to enter in a numeric form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
How odd - here I get 12 3.45 in both Word 2003 and 2007 using that switch?
-- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Graham, Works for me - 12345 gets displayed as 12 345.00, which accords with the OP's requirement (I always require two decimal places, even if the user only inputs the integer value). "Graham Mayor" wrote in message ... You could do that but you are reliant on the user entering the decimal. Try it with 12345 -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Owen, If you make the formfield type numeric, you can specify the number format as: ### ### ##0.00 "Owen" wrote in message ... I am attempting to determine the format code i need to enter in a numeric form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#8
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Same here, even if I modify the digit grouping symbol in the Control
PanelRegional and Language Options. It was only after trying all sorts of formatting switches that I resorted to creating the macro. Wait a minute, it does work if you use Ctrl+Shift+Spacebar to insert the space in the formatting switch # ##0.0 Also, the switch shown above is all that is required no matter how many digits are contained in the number -- 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, originally posted via msnews.microsoft.com "Graham Mayor" wrote in message ... How odd - here I get 12 3.45 in both Word 2003 and 2007 using that switch? -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Graham, Works for me - 12345 gets displayed as 12 345.00, which accords with the OP's requirement (I always require two decimal places, even if the user only inputs the integer value). "Graham Mayor" wrote in message ... You could do that but you are reliant on the user entering the decimal. Try it with 12345 -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org macropod wrote: Hi Owen, If you make the formfield type numeric, you can specify the number format as: ### ### ##0.00 "Owen" wrote in message ... I am attempting to determine the format code i need to enter in a numeric form field. The following are my parameters: The format i am trying to achieve is: ### ### ###.## I always require two decimal places, even if the user only inputs the integer value. I require a space between each 3 consecutive digits. I do not want to show any leading zeros. For example, the formatted fields appear as 456 345.23 2 456 345.23 Thanks in advance for any help, i do not seem to be able to achieve the desired result through using # or 0 or any combination of them. |
#9
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Doug Robbins - Word MVP wrote:
Wait a minute, it does work if you use Ctrl+Shift+Spacebar to insert the space in the formatting switch # ##0.0 Well spotted! That works for me too -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org |
#10
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
FWIW I tried to find out what the discrepancy between the two results of
macropod's ### ### ##0.00 approach. In essence, it works the macropod way on Word 2000, and quite likely on versions earlier than that, and on Mac Word - at least 2008, so I would guess earlier versions as well. It works the Graham/Doug way on Windows versions of Word from 2002 onwards. I don't have a different/better solution, sorry. There's one potential problem with both of the solutions which is that the input number is treated as more than one number once it is split using either type of space. In other words, if you have form fields text1 formatted with the space/non-breaking space, and text2 as a calculation set to =text1, text2 will be the sum of the separated parts of the number in text1. Which may or may not affect the OP. Peter Jamieson http://tips.pjmsn.me.uk Doug Robbins - Word MVP wrote: Same here, even if I modify the digit grouping symbol in the Control PanelRegional and Language Options. It was only after trying all sorts of formatting switches that I resorted to creating the macro. Wait a minute, it does work if you use Ctrl+Shift+Spacebar to insert the space in the formatting switch # ##0.0 Also, the switch shown above is all that is required no matter how many digits are contained in the number |
#11
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
What if your system is set to display multidigit numbers in a way
other than the US standard, i.e. with spaces (or periods = full stops) rather than commas every three digits? On Aug 4, 5:17*am, Peter Jamieson wrote: FWIW I tried to find out what the discrepancy between the two results of macropod's ### ### ##0.00 approach. In essence, it works the macropod way on Word 2000, and quite likely on versions earlier than that, and on Mac Word - at least 2008, so I would guess earlier versions as well. It works the Graham/Doug way on Windows versions of Word from 2002 onwards. I don't have a different/better solution, sorry. There's one potential problem with both of the solutions which is that the input number is treated as more than one number once it is split using either type of space. In other words, if you have form fields text1 formatted with the space/non-breaking space, and text2 as a calculation set to =text1, text2 will be the sum of the separated parts of the number in text1. Which may or may not affect the OP. Peter Jamieson http://tips.pjmsn.me.uk Doug Robbins - Word MVP wrote: Same here, even if I modify the digit grouping symbol in the Control PanelRegional and Language Options. *It was only after trying all sorts of formatting switches that I resorted to creating the macro. Wait a minute, it does work if you use Ctrl+Shift+Spacebar to insert the space in the formatting switch # ##0.0 Also, the switch shown above is all that is required no matter how many digits are contained in the number- |
#12
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Well, you can obviously try it for yourself, but
a. broadly speaking, Word will honour at least some combinations of "decimal symbol" and "digit grouping symbol". b. as far as I know there is no obvious or documented "region-neutral" way to specify a format string, so either - there is a way, waiting to be revealed/discovered or - you have to use the correct character for the "decimal symbol" in both the number you want to format, and the format string. You have to use the appropriate "digit grouping symbol" in the format string c. In regional settings, there are actually two "decimal symbols" and two "digit grouping symbols" - one for "numbers" and one for "currency". AFAICS Word honours the "number" one even when the format includes a currency symbol. I'm not sure there are any relevant circumstances in which Word can tell that something is a "currency" rather than a "number" d. You do seem to be able to use a space as the "digit grouping symbol" and put a space in a numeric format, and it does seem to work. (A reason I am cautious here is because the last time I looked at that in any detail, quite a few years ago, there was a problem with Canandian French, which does use a space as the "digit grouping symbol", and I don't think a solution was found) e. Unfortunately, the same format string does not appear to work when you insert it in the properties of a form field. f. even if it did, I wouldn't find it easy to recommend modifying the regional settings purely for that specific use or set of uses. But as ever, if there is no other way,... Peter Jamieson http://tips.pjmsn.me.uk Peter T. Daniels wrote: What if your system is set to display multidigit numbers in a way other than the US standard, i.e. with spaces (or periods = full stops) rather than commas every three digits? On Aug 4, 5:17 am, Peter Jamieson wrote: FWIW I tried to find out what the discrepancy between the two results of macropod's ### ### ##0.00 approach. In essence, it works the macropod way on Word 2000, and quite likely on versions earlier than that, and on Mac Word - at least 2008, so I would guess earlier versions as well. It works the Graham/Doug way on Windows versions of Word from 2002 onwards. I don't have a different/better solution, sorry. There's one potential problem with both of the solutions which is that the input number is treated as more than one number once it is split using either type of space. In other words, if you have form fields text1 formatted with the space/non-breaking space, and text2 as a calculation set to =text1, text2 will be the sum of the separated parts of the number in text1. Which may or may not affect the OP. Peter Jamieson http://tips.pjmsn.me.uk Doug Robbins - Word MVP wrote: Same here, even if I modify the digit grouping symbol in the Control PanelRegional and Language Options. It was only after trying all sorts of formatting switches that I resorted to creating the macro. Wait a minute, it does work if you use Ctrl+Shift+Spacebar to insert the space in the formatting switch # ##0.0 Also, the switch shown above is all that is required no matter how many digits are contained in the number- |
#13
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
If you use Alt+0160 to set the Digit separator in the Regional and Language
Options item on the Control Panel, a non-breaking space is used as the digits separator and then the entry is treated as one number when used as an input to another Calculation type formfield. -- 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, originally posted via msnews.microsoft.com "Peter T. Daniels" wrote in message ... What if your system is set to display multidigit numbers in a way other than the US standard, i.e. with spaces (or periods = full stops) rather than commas every three digits? On Aug 4, 5:17 am, Peter Jamieson wrote: FWIW I tried to find out what the discrepancy between the two results of macropod's ### ### ##0.00 approach. In essence, it works the macropod way on Word 2000, and quite likely on versions earlier than that, and on Mac Word - at least 2008, so I would guess earlier versions as well. It works the Graham/Doug way on Windows versions of Word from 2002 onwards. I don't have a different/better solution, sorry. There's one potential problem with both of the solutions which is that the input number is treated as more than one number once it is split using either type of space. In other words, if you have form fields text1 formatted with the space/non-breaking space, and text2 as a calculation set to =text1, text2 will be the sum of the separated parts of the number in text1. Which may or may not affect the OP. Peter Jamieson http://tips.pjmsn.me.uk Doug Robbins - Word MVP wrote: Same here, even if I modify the digit grouping symbol in the Control PanelRegional and Language Options. It was only after trying all sorts of formatting switches that I resorted to creating the macro. Wait a minute, it does work if you use Ctrl+Shift+Spacebar to insert the space in the formatting switch # ##0.0 Also, the switch shown above is all that is required no matter how many digits are contained in the number- |
#14
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Hi Peter,
The differences in behaviour suggest there's a bug in some Word versions - One should at least expect Word 2008 and 2007 to behave the same - my preference (and I suspect most users' preference) for a fix would be to have all versions work the same as Word 2008. -- Cheers macropod [Microsoft MVP - Word] "Peter Jamieson" wrote in message ... FWIW I tried to find out what the discrepancy between the two results of macropod's ### ### ##0.00 approach. In essence, it works the macropod way on Word 2000, and quite likely on versions earlier than that, and on Mac Word - at least 2008, so I would guess earlier versions as well. It works the Graham/Doug way on Windows versions of Word from 2002 onwards. I don't have a different/better solution, sorry. There's one potential problem with both of the solutions which is that the input number is treated as more than one number once it is split using either type of space. In other words, if you have form fields text1 formatted with the space/non-breaking space, and text2 as a calculation set to =text1, text2 will be the sum of the separated parts of the number in text1. Which may or may not affect the OP. Peter Jamieson http://tips.pjmsn.me.uk Doug Robbins - Word MVP wrote: Same here, even if I modify the digit grouping symbol in the Control PanelRegional and Language Options. It was only after trying all sorts of formatting switches that I resorted to creating the macro. Wait a minute, it does work if you use Ctrl+Shift+Spacebar to insert the space in the formatting switch # ##0.0 Also, the switch shown above is all that is required no matter how many digits are contained in the number |
#15
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Yes, the Mac Word code was forked from the Windows version quite some
time ago - I thought it was from Word 2000, which would make sense given what I found. Whether what is now considered to be a "legacy" feature - in the Windows version at least - will be "fixed" so the versions are back in line in the next version of Mac Word when it gets VBA back I have no idea. Peter Jamieson macropod wrote: Hi Peter, The differences in behaviour suggest there's a bug in some Word versions - One should at least expect Word 2008 and 2007 to behave the same - my preference (and I suspect most users' preference) for a fix would be to have all versions work the same as Word 2008. |
#16
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Form Field Number Format Problem
Well spotted again!
Peter Jamieson http://tips.pjmsn.me.uk Doug Robbins - Word MVP wrote: If you use Alt+0160 to set the Digit separator in the Regional and Language Options item on the Control Panel, a non-breaking space is used as the digits separator and then the entry is treated as one number when used as an input to another Calculation type formfield. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format form field for phone number | Microsoft Word Help | |||
puzzling number format problem in conditional field | Mailmerge | |||
form field format for phone number | Microsoft Word Help | |||
Syntax for Number format in the Text Form Field Options | Microsoft Word Help | |||
form field,can I add a telephone number under the number field w/d | Microsoft Word Help |