Decimal Point Fixing

[TheWizard]

Hi,

I suppose this is easy enough if you know how but I don't have any help files
available.

I need to set an output to a text box to two decimal places is there some sort
of format command?

JD

[J French]

Re: Decimal Point Fixing

On Tue, 24 Jun 2003 16:31:04 +0100, "TheWizard"
<the.wizard@I.H ATE.SPAM.blueyo nder.co.uk> wrote:
[color=blue]
>Hi,
>
>I suppose this is easy enough if you know how but I don't have any help files
>available.
>
>I need to set an output to a text box to two decimal places is there some sort
>of format command?[/color]

Format() - if you have the stomach for it

Personally I detest the VBA Format and override it with something I
first wrote in about 1987

This is *very* old code, but it has been working for years
- I try not to tinker with core stuff - unless necessary

- you will need to provide the obvious 'helper' functions

A$ = Format$( "###.##", "1.175" ) - Note: all three are strings


' ############### ############### ############### ###########
'
'
'
Function Format(Fmt$, HoldP$) As String

' returns P$ formatted to "##,###.##" for example
'
' ~ - Dealers "100.00 " or "100.001 " or "100.00123"
' $F - French Format
' $T - Trimmed Numeric
' $J in both Format and print string suppresses decimals
' $JD as above but appends "." as last char in format
' $N - Truncates off decimals
' $0 - Right packs with 0
' $L - Left Justifies within field
' @ - Returns Blank Field if empty
' $A - Adjusts 0 decimals to 2 decimals if not a round number

' 12/11/87 JF
' 7/3/88 JF - @ , $F & $T added
' 5/4/88 JF - $N added - equivalent of FIX( A# )
' 4/5/88 JF - fixed -.00 to -0.00
' 21/12/90 JF - $0 added
' 7/2/91 JF - $L added
' 10/4/91 JF - $A re-instated (was first introduced 5/10/90)
' 24/11/91 JF - "0000-10" fixed to "-000010"
' 3/2/92 JF - Fix for bad chars ie %
' 22/5/92 - extended to #!& as well - also $JD added
' 27/3/00 JF - Fix for VB5 bad numbers
' 24/5/00 JF - Fix for error in last fix - Must do Abs
' 29//7/02 JF - Fix DPos was P in "-.1" to "-0.1" below

Dim P$, L%, Commas%, Dealers%, BlankNull%, FrenchFormat%, _
JapField%, TrimIt%, JapTrailDP%, FixIt%, PackZero%, _
LeftJust%, AdjustDec%, DP_Pos%, DP%, N#, Q#, i%, DPPos%, E%, _
PercentFlag%


P$ = HoldP$
Call StrReplaceAnyCh r(P$, "%#!&", "x")
L = Len(Fmt$)
Commas = InStr(Fmt$, ",")
Dealers = InStr(Fmt$, "~")
BlankNull = InStr(Fmt$, "@")
PercentFlag = InStr(Fmt$, "%")

FrenchFormat = InStr(Fmt$, "$F") ' 9.999,00
TrimIt = InStr(Fmt$, "$T") ' Trimmed Numeric field
JapField = InStr(Fmt$, "$J")
If JapField Then _
JapTrailDP = InStr(Fmt$, "$JD")

FixIt = InStr(Fmt$, "$N")
PackZero = InStr(Fmt$, "$0")
LeftJust = InStr(Fmt$, "$L")
AdjustDec = InStr(Fmt$, "$A")

DP_Pos = InStr(Fmt$, ".")
DP = 0
If DP_Pos Then _
DP = L - DP_Pos

Call StrRemoveStr(P$ , ",") ' Strip ','

If JapField Then _
If InStr(P$, "$J") Then _
DP = 0 ' Integer regardless

N# = Val(P$)

If AdjustDec Then _
If N# <> Int(N#) Then _
DP = 2


If FixIt Then
DP = 0 ' for test keys
N# = Fix(N#)
End If

Q# = 10# ^ DP ' round it first
N# = Int(Abs(N#) * Q# + 0.500001) / Q# * Sgn(N#) ' Must do Abs
If N# = 0 Then
If BlankNull Then
P$ = Space$(L)
GoTo QUIT
End If
End If

P$ = Str$(N#)
If DP = 0 Then _
GoTo NO_DECIMALS

' here we sort out leading / trailing part of the decimals

AGAIN:
DPPos = InStr(P$, ".")
If DPPos = 0 Then _
P$ = P$ + ".": _
GoTo AGAIN

If DPPos - 1 > 0 Then _
If Mid$(P$, DPPos - 1, 1) = " " Then _
Mid$(P$, DPPos - 1, 1) = "0"

If Mid$(P$, DPPos - 1, 1) = "-" Then
P$ = "-0" + Mid$(P$, DPPos) ' 29/7/02 JF DPos was P
DPPos = DPPos + 1
End If


P$ = Mid$(P$ + String$(DP, "0"), 1, DPPos + DP)
If Dealers Then
E = Len(P$)
While E > DPPos + 2 And Val(Mid$(P$, E, 1)) = 0
Mid$(P$, E, 1) = " "
E = E - 1
Wend
End If

NO_DECIMALS:
If JapTrailDP Then _
If InStr(P$, ".") = 0 Then _
P$ = P$ + "." ' 999999.


DP = InStr(P$, ".")
If DP = 0 Then ' was INSTR( P$+".", "." )
DP = Len(P$) + 1
End If

If Commas Then
i = DP - 4
While i > 0
If InStr("01234567 89", Mid$(P$, i, 1)) Then _
P$ = Mid$(P$, 1, i) + "," + Mid$(P$, i + 1)
i = i - 3
Wend
End If

If FrenchFormat Then
DP = InStr(P$, ".")
Call StrReplaceStr(P $, ",", ".")
If DP Then _
Mid$(P$, DP, 1) = ","
End If


If Len(LTrim$(P$)) > L Then
P$ = String$(L, "!") ' E R R O R
Else
P$ = Right$(String$( L, " ") + P$, L)
End If

If TrimIt Then _
P$ = Trim$(P$)

If PackZero Then
Q = InStr(P$, "-")
Call StrReplaceStr(P $, " ", "0")
If Q > 1 Then
Mid$(P$, Q) = "0" ' 24/11/91 JF
Mid$(P$, 1) = "-"
End If
End If

If LeftJust Then _
LSet P$ = Trim$(P$)

If PercentFlag Then _
P$ = Mid$(P$, 2) + "%"

QUIT:
Format$ = P$
End Function

[Mauro]

Re: Decimal Point Fixing

Use FormatNumber()

Mauro

"TheWizard" <the.wizard@I.H ATE.SPAM.blueyo nder.co.uk> wrote in message
news:S3_Ja.3323 $5b3.679@news-binary.blueyond er.co.uk...[color=blue]
> Hi,
>
> I suppose this is easy enough if you know how but I don't have any help[/color]
files[color=blue]
> available.
>
> I need to set an output to a text box to two decimal places is there some[/color]
sort[color=blue]
> of format command?
>
> JD
>
>[/color]

[Hemant Pathak]

Simpy use Format$ function
const fNumber="###,## .00"
a=inputbox("Ent er nay Number")
Text1.text=form at(a,fnumber)