On Tue, 24 Jun 2003 16:31:04 +0100, "TheWizard"
<th********@I.HATE.SPAM.blueyonder.co.uk> wrote:
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?
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 StrReplaceAnyChr(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("0123456789", 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