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