Hi,
I am using Crystal Report 8.5 to prepare my Report and I want to convert the figures under
currency to be converted automatically while printing the report. e.g If I enter 234 in my field kk than under text field hh it should be automatically written as two hundred thirty four.
Pl help
(k.s.)
Jump to PostHi
It is my own coding to convert number to string. I cannot sure to work this code properly. Any way try this.> Create a new Module and Paste the below codes
Public Function UnitString(ByVal Ind As Integer) As String Dim ToStr As Variant ToStr …
Jump to PostHi Kehar,
Check this :
You need to create a New Formula for that..
Regards
Veena
Hi
It is my own coding to convert number to string. I cannot sure to work this code properly. Any way try this.
> Create a new Module and Paste the below codes
Public Function UnitString(ByVal Ind As Integer) As String
Dim ToStr As Variant
ToStr = Array("Zero", "One", "Two", "Three", "Four", "Five", _
"Six", "Seven", "Eight", "Nine", "Ten", _
"Eleven", "Tweleve", "Thirteen", "Fourteen", "Fifteen", _
"Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty")
UnitString = ToStr(Ind)
End Function
Public Function TenString(ByVal Ind As Integer) As String
Dim ToStr As Variant
ToStr = Array("Ten", "Twenty", "Thirty", "Fourty", "Fifty", _
"Sixty", "Seventy", "Eighty", "Ninety" _
)
TenString = ToStr(Ind)
End Function
Public Function PlaceString(ByVal Ind As Integer) As String
Dim ToStr As Variant
ToStr = Array("Unit", "Tenth", "Hundred", _
"Thousand", "Laksh", "Crore", _
"Hundred" _
)
PlaceString = ToStr(Ind)
End Function
Public Function ToString(ByVal No As Long) As String
Dim ToStr As String
'Dim No As Long
Dim Value As Long
Dim Divisor As Long
Dim i As Long
Divisor = 1000000000
i = 6
Do While (No >= 1000)
Value = Int(No / Divisor)
No = No Mod Divisor
Divisor = Int(Divisor / 100)
If (Value <> 0) Then
ToStr = ToStr & NumberToString100(Value) & " " & PlaceString(i) + " "
End If
i = i - 1
Loop
Value = Int(No / 100)
No = No Mod 100
If (Value <> 0) Then
ToStr = ToStr & NumberToString100(Value) & " " & PlaceString(i) + " "
End If
i = i - 1
If No <> 0 Then
ToStr = ToStr & NumberToString100(No)
End If
ToString = Trim(ToStr)
End Function
Private Function NumberToString100(ByVal No As Integer) As String
Dim ToStr As String
Dim Ten As Integer, Unit As Integer
If (No <= 20) Then
NumberToString100 = UnitString(No)
Exit Function
End If
No = No Mod 100
Ten = Int(No / 10)
No = No Mod 10
Unit = No
If (Ten >= 2) Then
ToStr = ToStr & " " & TenString(Ten - 1)
If (Unit > 0) Then
ToStr = ToStr & " " & UnitString(Unit)
End If
ElseIf (Ten > 0) Then
ToStr = ToStr & " " & UnitString((Ten * 10) + Unit)
ElseIf (Unit > 0) Then
ToStr = ToStr & " " & UnitString(Unit)
End If
NumberToString100 = Trim(ToStr)
End FunctionExample MsgBox ToString (1000)
Copy is code in module.
---------------------------------
--------------- code on Form1---------
Private Sub CmdGo_Click()
dim strword as string
strword = NumberToWords(val(text1.text))
msgbox strword
end sub
------ Name :: NumberToWord--------------
Option Explicit
Dim str1, str2
Public Function NumberToWords(Number As Double) As String
Dim intlen As Integer
Dim intDivValue As Single
Dim leftValue As String, curValue As String
Dim strComplete As String, i As Integer, blnretval As Boolean
Dim intPaise As String, strPaise As String
blnretval = False
If Len(CStr(Number)) > 13 Then
NumberToWords = "Out of Limit"
Exit Function
ElseIf Number = 0 Then
NumberToWords = "Zero only"
Exit Function
End If
If InStr(1, Number, ".") > 0 Then
intPaise = CStr(Mid(Number, InStr(1, Number, ".") + 1, 2))
str1 = arrvalue2(intPaise, blnretval)
If blnretval = False Then
If Len(CStr(intPaise)) > 1 Then
str2 = arrvalue1(Right(intPaise, 1))
End If
Else
str2 = ""
End If
strPaise = " and " & str1 & " " & str2 & " Paise"
Number = Mid(Number, 1, InStr(1, Number, ".") - 1)
Else
End If
intlen = Len(Trim(Number))
i = 1
While intlen > 0
If intlen > 3 Then
intDivValue = CSng(intlen / 2)
If intDivValue > 2 And InStr(1, intDivValue, ".") > 0 Then
curValue = Mid(Number, i, 2)
str1 = arrvalue2(curValue, blnretval)
If blnretval = False Then
str2 = arrvalue1(Right(curValue, 1))
Else
str2 = ""
End If
strComplete = Trim$(strComplete & " " & str1 & " " & str2)
intlen = intlen - Len(curValue)
i = i + 2
strComplete = Trim$(strComplete & " " & IIf(curValue > 0, arrvalue3(intlen), ""))
Else
curValue = Mid(Number, i, 1)
str2 = arrvalue1(Right(curValue, 1))
strComplete = strComplete & " " & str2
intlen = intlen - Len(curValue)
i = i + 1
strComplete = strComplete & " " & arrvalue3(intlen)
End If
ElseIf intlen = 3 Then
curValue = Mid(Number, i, 1)
str1 = arrvalue1(Left(curValue, 1))
strComplete = Trim(strComplete & " " & str1 & " " & IIf(curValue > 0, arrvalue3(2), ""))
intlen = intlen - Len(curValue)
ElseIf intlen = 2 Then
str1 = arrvalue2(Right(Number, 2), blnretval)
If blnretval = False Then
str2 = arrvalue1(Right(Number, 1))
Else
str2 = ""
End If
strComplete = Trim(strComplete & " " & str1 & " " & str2)
GoTo l
ElseIf intlen = 1 Then
str1 = arrvalue1(Number)
strComplete = Trim(strComplete & " " & str1)
GoTo l
End If
Wend
l:
NumberToWords = strComplete & strPaise & " only"
End Function
Private Function arrvalue1(ByVal s As String) As String
Dim arrnumber(0 To 10) As String
arrnumber(0) = ""
arrnumber(1) = "One"
arrnumber(2) = "two"
arrnumber(3) = "three"
arrnumber(4) = "Four"
arrnumber(5) = "Five"
arrnumber(6) = "Six"
arrnumber(7) = "Seven"
arrnumber(8) = "Eight"
arrnumber(9) = "Nine"
arrvalue1 = arrnumber(CInt(s))
Erase arrnumber
End Function
Private Function arrvalue2(ByVal s As String, Optional result As Boolean = False) As String
Dim arrnumber(1 To 19) As String
If CInt(s) > 10 And CInt(s) < 20 Then
arrnumber(11) = "Eleven"
arrnumber(12) = "Twelve"
arrnumber(13) = "Thirteen"
arrnumber(14) = "Forteen"
arrnumber(15) = "Fifteen"
arrnumber(16) = "sixteen"
arrnumber(17) = "Seventeen"
arrnumber(18) = "Eighteen"
arrnumber(19) = "Nineteen"
result = True
Else
arrnumber(1) = "Ten"
arrnumber(2) = "Twenty"
arrnumber(3) = "Thirty"
arrnumber(4) = "Forty"
arrnumber(5) = "Fifty"
arrnumber(6) = "sixty"
arrnumber(7) = "Seventy"
arrnumber(8) = "Eighty"
arrnumber(9) = "Ninety"
s = Left(s, 1)
result = False
End If
If s <> 0 Then
arrvalue2 = arrnumber(CInt(s))
End If
Erase arrnumber
End Function
Private Function arrvalue3(s As Integer) As String
Dim arrnumber(2 To 11) As String
arrnumber(2) = "Hundred"
arrnumber(3) = "Thousand"
arrnumber(5) = "Lakh"
arrnumber(7) = "Crores"
arrnumber(9) = "Arab"
arrnumber(11) = "Kharab"
arrvalue3 = arrnumber(CInt(s))
Erase arrnumber
End Function
''how to conver figures to words
Public Function funcAmt(AMT As Currency) As String
Dim i As Integer
Dim j As Integer
Dim strnumber As String
Dim str1 As String
Dim strno As String
Dim str2 As String
Dim str3 As String
strno = CStr(AMT)
'If KeyAscii = 13 Then
If Len(strno) > 2 Then str3 = "and" Else str3 = ""
For i = 1 To Len(strno)
If Not Mid(strno, 1, 1) = 0 Then
If Len(strno) > 10 Then
MsgBox "Overflow", vbInformation, "Sorry"
Exit Function
End If
If Len(strno) = 10 Then
str1 = "Million"
strnumber = strnumber & " " & Units(Mid(strno, 1, 1)) & " " & str1
strno = Mid(strno, 2)
ElseIf Len(strno) = 9 Then
If Val(Mid(strno, 1, 2)) < 20 Then
strnumber = strnumber & " " & Tens(Mid(strno, 1, 2)) & " " & "Crores"
strno = Mid(strno, 3)
ElseIf Val(Mid(strno, 2, 1)) = 0 Then
strnumber = strnumber & " " & Tens(Mid(strno, 1, 1)) & " " & "Crores"
strno = Mid(strno, 3)
Else
strnumber = strnumber & " " & Tens(Mid(strno, 1, 1))
strno = Mid(strno, 2)
End If
ElseIf Len(strno) = 8 Then
If Val(Mid(strno, 1, 1)) > 1 Then str1 = "Crores" Else str1 = "Crore"
strnumber = strnumber & " " & Units(Mid(strno, 1, 1)) & " " & str1
strno = Mid(strno, 2)
ElseIf Len(strno) = 7 Then
If Val(Mid(strno, 1, 2)) < 20 Then
strnumber = strnumber & " " & Tens(Mid(strno, 1, 2)) & " " & "Lakhs"
strno = Mid(strno, 3)
ElseIf Val(Mid(strno, 2, 1)) = 0 Then
strnumber = strnumber & " " & Tens(Mid(strno, 1, 1)) & " " & "Lakhs"
strno = Mid(strno, 3)
Else
strnumber = strnumber & " " & Tens(Mid(strno, 1, 1))
strno = Mid(strno, 2)
End If
ElseIf Len(strno) = 6 Then
If Val(Mid(strno, 1, 1)) > 1 Then str1 = "Lakhs" Else str1 = "Lakh"
strnumber = strnumber & " " & Units(Mid(strno, 1, 1)) & " " & str1
strno = Mid(strno, 2)
ElseIf Len(strno) = 5 Then
If Val(Mid(strno, 1, 2)) < 20 Then
strnumber = strnumber & " " & Tens(Mid(strno, 1, 2)) & " " & "Thousand"
strno = Mid(strno, 3)
ElseIf Val(Mid(strno, 2, 1)) = 0 Then
strnumber = strnumber & " " & Tens(Mid(strno, 1, 1)) & " " & "Thousand"
strno = Mid(strno, 3)
Else
strnumber = strnumber & " " & Tens(Mid(strno, 1, 1))
strno = Mid(strno, 2)
End If
ElseIf Len(strno) = 4 Then
strnumber = strnumber & " " & Units(Mid(strno, 1, 1)) & " " & "Thousand"
strno = Mid(strno, 2)
ElseIf Len(strno) = 3 Then
strnumber = strnumber & " " & Units(Mid(strno, 1, 1)) & " " & "Hundred"
strno = Mid(strno, 2)
ElseIf Len(strno) = 2 Then
If Val(Mid(strno, 1, 2)) < 20 Then
strnumber = strnumber & " " & str3 & " " & Tens(Mid(strno, 1, 2))
strno = Mid(strno, 3)
ElseIf Val(Mid(strno, 2, 1)) = 0 Then
strnumber = strnumber & " " & str3 & " " & Tens(Mid(strno, 1, 1))
strno = Mid(strno, 3)
Else
strnumber = strnumber & " " & str3 & " " & Tens(Mid(strno, 1, 1))
strno = Mid(strno, 2)
End If
ElseIf Len(strno) = 1 Then
strnumber = strnumber & " " & str2 & " " & Units(Mid(strno, 1, 1))
strno = Mid(strno, 2)
End If
Else
If Len(strno) = 2 Then
str2 = "and"
Else
str2 = ""
End If
strno = Mid(strno, 2)
End If
Next
' End If
FuncAmount = strnumber
Text2.Text = FuncAmount
End Function
Private Sub Command1_Click()
Call funcAmt(Text1.Text)
End Sub
Public Function Tens(i As Integer) As String
If i = 10 Then Tens = "Ten"
If i = 11 Then Tens = "Eleven"
If i = 12 Then Tens = "Twelve"
If i = 13 Then Tens = "Thirteen"
If i = 14 Then Tens = "Fourteen"
If i = 15 Then Tens = "Fifteen"
If i = 16 Then Tens = "Sixteen"
If i = 17 Then Tens = "Seventeen"
If i = 18 Then Tens = "Eighteen"
If i = 19 Then Tens = "Nineteen"
If i = 2 Then Tens = "Twenty"
If i = 3 Then Tens = "Thirty"
If i = 4 Then Tens = "Forty"
If i = 5 Then Tens = "Fifty"
If i = 6 Then Tens = "Sixty"
If i = 7 Then Tens = "Seventy"
If i = 8 Then Tens = "Eighty"
If i = 9 Then Tens = "Ninety"
End Function
Public Function Units(i As Integer) As String
If i = 1 Then Units = "One"
If i = 2 Then Units = "Two"
If i = 3 Then Units = "Three"
If i = 4 Then Units = "Four"
If i = 5 Then Units = "Five"
If i = 6 Then Units = "Six"
If i = 7 Then Units = "Seven"
If i = 8 Then Units = "Eight"
If i = 9 Then Units = "Nine"
End Function
I myself got the idea of converting figures to words in Crystal report.
Under formula bar of Crystal report type "ToWord (field name , 0)" this field name is the
database field saved as currency. But after using this it is converting in International forminstead of Indian form. e.g 112,000/- is writing as one hundred twelve thousand instead of
one lakh twelve thousand.
So how is this converted into Indian form
K.S
Hi Kehar,
Check this :
You need to create a New Formula for that..
Regards
Veena
Dear sir,
Thanks for application guidance.
Regards from
Jagdish H Patel
Hello
I am too searching for a syntax to convert figures into words
I have just started learning c# and i have made a small program that converts figures up to 100000 into words
If u gimme your email i cam mail you
Hi,
I am using Crystal Report 8.5 to prepare my Report and I want to convert the figures under
currency to be converted automatically while printing the report. e.g If I enter 234 in my field kk than under text field hh it should be automatically written as two hundred thirty four.Pl help
(k.s.)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.