Form Field Number Format Problem

[Owen]

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.

 

[Doug Robbins - Word MVP]

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

 

[macropod]

Hi Owen,

If you make the formfield type numeric, you can specify the number format as:
### ### ##0.00

 

[Graham Mayor]

You could do that but you are reliant on the user entering the decimal. Try
it with 12345

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


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

 

[Owen]

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.

 

[macropod]

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]

How odd - here I get 12 3.45 in both Word 2003 and 2007 using that switch?

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


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

 

[Doug Robbins - Word MVP]

Same here, even if I modify the digit grouping symbol in the Control
Panel>Regional 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]

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


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

 

[Peter Jamieson]

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

 

[Peter T. Daniels]

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?

 

[Peter Jamieson]

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

 

[Doug Robbins - Word MVP]

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
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?

 

[macropod]

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.

 

[Peter Jamieson]

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

 

[Peter Jamieson]

Well spotted again!

Peter Jamieson

http://tips.pjmsn.me.uk