Display a number in accounting format in a message box (1 Viewer)

Hi Everyone,

As we all know Access do not display numbers in accounting format (NGN1,234.78) in a message box. I developed thes functions to do just that. The functions are Putcommas and IntCommas. The Listings are as follows:

Function PutCommas(Amt As String)
Dim DotPos As Long, StrLen As Long
Dim StrFin As String, NosCom As Long
Dim StrDec As String, LngDec As Single
If Len(CStr(Int(CDbl(Amt)))) <= 3 Then ' Amt < 1000 no need of commas
StrFin = CStr(Round(CDbl(Amt), 2))
GoTo ENDD
End If

StrLen = Len(Amt) ' How many digits is Amt?
DotPos = InStr(Amt, ".") ' Decimal position

If DotPos = 0 Then 'Amt is a whole number
StrFin = IntCommas(Amt) & ".00" 'Put commas in 3 digits

Else
StrDec = Mid(Amt, DotPos)
LngDec = Round(CDec(StrDec), 2)
StrDec = "." & Right(CStr(LngDec), 2)

StrFin = IntCommas(CStr(Int(CDbl(Amt))))

StrFin = StrFin & StrDec
End If

ENDD:
PutCommas = StrFin

End Function

Function IntCommas(IntPart As String)
Dim NosCom As Long, n, x
Dim StrCvt As String, StrLen As Long
StrLen = Len(IntPart)
NosCom = Int(StrLen / 3) 'Number of commas
n = NosCom
x = 1

Do Until n = 0
StrCvt = "," & Mid(IntPart, StrLen - 3 * x + 1, 3) & StrCvt
n = n - 1
x = x + 1
Loop

If StrLen = NosCom * 3 Then
StrCvt = Mid(StrCvt, 2, Len(StrCvt) - 1)
Else
StrCvt = Left(IntPart, StrLen - 3 * NosCom) & StrCvt
End If
IntCommas = StrCvt

End Function

Example:
Display "Your Balance is NGN20,456.45" in a Message Box

1. Have the Amount in a string variable e.g Balance
2. create the message you want to display in a string variable e.g
strMsg = "Your Balance is NGN" & PutCommas(Balance)
3. Create the Message Box
MsgBox strMsg
4. Your Messge Box should display Balance with commas and two digits beyond the decimal.

Note: Replace NGN with your currency symbol

I hope someone find this useful.

Engr. Aliyu Muhammad Katsina