AliyuKatsina
Registered User.
- Local time
- Today, 04:23
- Joined
- Dec 31, 2009
- Messages
- 73
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
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