Change Numbers in To Text

J

jadeja

Registered User.
Local time
Yesterday, 18:31
Joined
Sep 23, 2012
Messages
11
Hello.
I have one field called Bill_Amount. Data Type is Currency and format is set as a Fixed with 3 Decimal Places in the form.

I want a text box which will display the Bill_Amount in the Text.
e.g Bill_Amount = 123.251 Text box will display One Hundred Twenty Three Rial and Two Hundred Fifty One Baiza.

Please help me how to i set this. I don't know anything about codes.

Thanks.
 
No, It shows error.
I created a module named as English. Then attached the module with text box.
=English([Bill_Amount])

When i run the form it displays as #Name?

Please explain in detail as i am new to access and don't know much in codes.

thanks
 
Your MODULE cannot be named the same as the function it contains!
 
I changed it to NumToWords.
Even though it displays results as #Error.

This is the code i have used.

Function English(ByVal N As Currency) As String
Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion

If (N = 0@) Then English = "zero": Exit Function

Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1

If (N >= Trillion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
N = N - Int(N / Trillion) * Trillion ' Mod overflows
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Billion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
N = N - Int(N / Billion) * Billion ' Mod still overflows
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Million) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
N = N Mod Million
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= Thousand) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & " "
End If

If (N >= 1@) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N)
End If

If (Frac = 0@) Then
Buf = Buf & " exactly"
ElseIf (Int(Frac * 100@) = Frac * 100@) Then
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 100@, "00") & "/100"
Else
If AtLeastOne Then Buf = Buf & " and "
Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
End If

English = Buf
End Function

' Support function to be used only by English()
Private Function EnglishDigitGroup(ByVal N As Integer) As String
Const Hundred = " hundred"
Const One = "one"
Const Two = "two"
Const Three = "three"
Const Four = "four"
Const Five = "five"
Const Six = "six"
Const Seven = "seven"
Const Eight = "eight"
Const Nine = "nine"
Dim Buf As String: Buf = ""
Dim Flag As Integer: Flag = False

'Do hundreds
Select Case (N \ 100)
Case 0: Buf = "": Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select

If (Flag <> False) Then N = N Mod 100
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & " "
Else
EnglishDigitGroup = Buf
Exit Function
End If

'Do tens (except teens)
Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & "twenty": Flag = True
Case 3: Buf = Buf & "thirty": Flag = True
Case 4: Buf = Buf & "forty": Flag = True
Case 5: Buf = Buf & "fifty": Flag = True
Case 6: Buf = Buf & "sixty": Flag = True
Case 7: Buf = Buf & "seventy": Flag = True
Case 8: Buf = Buf & "eighty": Flag = True
Case 9: Buf = Buf & "ninety": Flag = True
End Select

If (Flag <> False) Then N = N Mod 10
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & "-"
Else
EnglishDigitGroup = Buf
Exit Function
End If

'Do ones and teens
Select Case (N)
Case 0: ' do nothing
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & "ten"
Case 11: Buf = Buf & "eleven"
Case 12: Buf = Buf & "twelve"
Case 13: Buf = Buf & "thirteen"
Case 14: Buf = Buf & "fourteen"
Case 15: Buf = Buf & "fifteen"
Case 16: Buf = Buf & "sixteen"
Case 17: Buf = Buf & "seventeen"
Case 18: Buf = Buf & "eighteen"
Case 19: Buf = Buf & "nineteen"
End Select

EnglishDigitGroup = Buf
End Function

---------------------

text1 = =NumToWords([Bill_Amount])
 
Do *NOT* mix single line code and multi-line code in a procedure.
ie: this is single line code:
If (N = 0@) Then English = "zero": Exit Function
This is multi-line code:
If...Then
...
End If
You also have some missing End If's
 
Here's what your code looks like properly indented and posted with code tags:
Code: 
Option Compare Database
Option Explicit

Function English(ByVal N As Currency) As String
   Const Thousand = 1000@
   Const Million = Thousand * Thousand
   Const Billion = Thousand * Million
   Const Trillion = Thousand * Billion

   If (N = 0@) Then English = "zero": Exit Function

   Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
   Dim Frac As Currency: Frac = Abs(N - Fix(N))
   If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
   Dim AtLeastOne As Integer: AtLeastOne = N >= 1

   If (N >= Trillion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
      N = N - Int(N / Trillion) * Trillion   ' Mod overflows
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Billion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
      N = N - Int(N / Billion) * Billion   ' Mod still overflows
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Million) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
      N = N Mod Million
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= Thousand) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
      N = N Mod Thousand
      If (N >= 1@) Then Buf = Buf & " "
   End If

   If (N >= 1@) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N)
   End If

   If (Frac = 0@) Then
      Buf = Buf & " exactly"
   ElseIf (Int(Frac * 100@) = Frac * 100@) Then
      If AtLeastOne Then Buf = Buf & " and "
      Buf = Buf & Format$(Frac * 100@, "00") & "/100"
   Else
      If AtLeastOne Then Buf = Buf & " and "
      Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
   End If

   English = Buf
End Function

' Support function to be used only by English()
Private Function EnglishDigitGroup(ByVal N As Integer) As String
   Const Hundred = " hundred"
   Const One = "one"
   Const Two = "two"
   Const Three = "three"
   Const Four = "four"
   Const Five = "five"
   Const Six = "six"
   Const Seven = "seven"
   Const Eight = "eight"
   Const Nine = "nine"
   Dim Buf As String: Buf = ""
   Dim Flag As Integer: Flag = False

   'Do hundreds
   Select Case (N \ 100)
      Case 0: Buf = "": Flag = False
      Case 1: Buf = One & Hundred: Flag = True
      Case 2: Buf = Two & Hundred: Flag = True
      Case 3: Buf = Three & Hundred: Flag = True
      Case 4: Buf = Four & Hundred: Flag = True
      Case 5: Buf = Five & Hundred: Flag = True
      Case 6: Buf = Six & Hundred: Flag = True
      Case 7: Buf = Seven & Hundred: Flag = True
      Case 8: Buf = Eight & Hundred: Flag = True
      Case 9: Buf = Nine & Hundred: Flag = True
   End Select

   If (Flag <> False) Then N = N Mod 100
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & " "
   Else
      EnglishDigitGroup = Buf
      Exit Function
   End If

   'Do tens (except teens)
   Select Case (N \ 10)
      Case 0, 1: Flag = False
      Case 2: Buf = Buf & "twenty": Flag = True
      Case 3: Buf = Buf & "thirty": Flag = True
      Case 4: Buf = Buf & "forty": Flag = True
      Case 5: Buf = Buf & "fifty": Flag = True
      Case 6: Buf = Buf & "sixty": Flag = True
      Case 7: Buf = Buf & "seventy": Flag = True
      Case 8: Buf = Buf & "eighty": Flag = True
      Case 9: Buf = Buf & "ninety": Flag = True
   End Select

   If (Flag <> False) Then N = N Mod 10
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & "-"
   Else
      EnglishDigitGroup = Buf
      Exit Function
   End If

   'Do ones and teens
   Select Case (N)
      Case 0:   ' do nothing
      Case 1: Buf = Buf & One
      Case 2: Buf = Buf & Two
      Case 3: Buf = Buf & Three
      Case 4: Buf = Buf & Four
      Case 5: Buf = Buf & Five
      Case 6: Buf = Buf & Six
      Case 7: Buf = Buf & Seven
      Case 8: Buf = Buf & Eight
      Case 9: Buf = Buf & Nine
      Case 10: Buf = Buf & "ten"
      Case 11: Buf = Buf & "eleven"
      Case 12: Buf = Buf & "twelve"
      Case 13: Buf = Buf & "thirteen"
      Case 14: Buf = Buf & "fourteen"
      Case 15: Buf = Buf & "fifteen"
      Case 16: Buf = Buf & "sixteen"
      Case 17: Buf = Buf & "seventeen"
      Case 18: Buf = Buf & "eighteen"
      Case 19: Buf = Buf & "nineteen"
   End Select

   EnglishDigitGroup = Buf
End Function
 
Once indented I did not see any missing "End If's" but convert the single line code to multi-line.
 
I made the changes to your code and it seems to work as I would expect:
Code: 
Option Compare Database
Option Explicit

Function English(ByVal N As Currency) As String
   Const Thousand = 1000@
   Const Million = Thousand * Thousand
   Const Billion = Thousand * Million
   Const Trillion = Thousand * Billion

   If (N = 0@) Then
      English = "zero":
      Exit Function
   End If

   Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
   Dim Frac As Currency: Frac = Abs(N - Fix(N))
   If (N < 0@ Or Frac <> 0@) Then
      N = Abs(Fix(N))
   End If
   
   Dim AtLeastOne As Integer: AtLeastOne = N >= 1

   If (N >= Trillion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
      N = N - Int(N / Trillion) * Trillion   ' Mod overflows
      If (N >= 1@) Then
         Buf = Buf & " "
      End If
   End If

   If (N >= Billion) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
      N = N - Int(N / Billion) * Billion   ' Mod still overflows
      If (N >= 1@) Then
         Buf = Buf & " "
      End If
   End If

   If (N >= Million) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
      N = N Mod Million
      If (N >= 1@) Then
         Buf = Buf & " "
      End If
   End If

   If (N >= Thousand) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
      N = N Mod Thousand
      If (N >= 1@) Then
         Buf = Buf & " "
      End If
   End If

   If (N >= 1@) Then
      Debug.Print N
      Buf = Buf & EnglishDigitGroup(N)
   End If

   If (Frac = 0@) Then
      Buf = Buf & " exactly"
   ElseIf (Int(Frac * 100@) = Frac * 100@) Then
      If AtLeastOne Then
         Buf = Buf & " and "
      End If
      Buf = Buf & Format$(Frac * 100@, "00") & "/100"
   Else
      If AtLeastOne Then
         Buf = Buf & " and "
      End If
      Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
   End If

   English = Buf
End Function

' Support function to be used only by English()
Private Function EnglishDigitGroup(ByVal N As Integer) As String
   Const Hundred = " hundred"
   Const One = "one"
   Const Two = "two"
   Const Three = "three"
   Const Four = "four"
   Const Five = "five"
   Const Six = "six"
   Const Seven = "seven"
   Const Eight = "eight"
   Const Nine = "nine"
   Dim Buf As String: Buf = ""
   Dim Flag As Integer: Flag = False

   'Do hundreds
   Select Case (N \ 100)
      Case 0: Buf = "": Flag = False
      Case 1: Buf = One & Hundred: Flag = True
      Case 2: Buf = Two & Hundred: Flag = True
      Case 3: Buf = Three & Hundred: Flag = True
      Case 4: Buf = Four & Hundred: Flag = True
      Case 5: Buf = Five & Hundred: Flag = True
      Case 6: Buf = Six & Hundred: Flag = True
      Case 7: Buf = Seven & Hundred: Flag = True
      Case 8: Buf = Eight & Hundred: Flag = True
      Case 9: Buf = Nine & Hundred: Flag = True
   End Select

   If (Flag <> False) Then
      N = N Mod 100
   End If
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & " "
   Else
      EnglishDigitGroup = Buf
      Exit Function
   End If

   'Do tens (except teens)
   Select Case (N \ 10)
      Case 0, 1: Flag = False
      Case 2: Buf = Buf & "twenty": Flag = True
      Case 3: Buf = Buf & "thirty": Flag = True
      Case 4: Buf = Buf & "forty": Flag = True
      Case 5: Buf = Buf & "fifty": Flag = True
      Case 6: Buf = Buf & "sixty": Flag = True
      Case 7: Buf = Buf & "seventy": Flag = True
      Case 8: Buf = Buf & "eighty": Flag = True
      Case 9: Buf = Buf & "ninety": Flag = True
   End Select

   If (Flag <> False) Then
      N = N Mod 10
   End If
   If (N > 0) Then
      If (Flag <> False) Then Buf = Buf & "-"
   Else
      EnglishDigitGroup = Buf
      Exit Function
   End If

   'Do ones and teens
   Select Case (N)
      Case 0:   ' do nothing
      Case 1: Buf = Buf & One
      Case 2: Buf = Buf & Two
      Case 3: Buf = Buf & Three
      Case 4: Buf = Buf & Four
      Case 5: Buf = Buf & Five
      Case 6: Buf = Buf & Six
      Case 7: Buf = Buf & Seven
      Case 8: Buf = Buf & Eight
      Case 9: Buf = Buf & Nine
      Case 10: Buf = Buf & "ten"
      Case 11: Buf = Buf & "eleven"
      Case 12: Buf = Buf & "twelve"
      Case 13: Buf = Buf & "thirteen"
      Case 14: Buf = Buf & "fourteen"
      Case 15: Buf = Buf & "fifteen"
      Case 16: Buf = Buf & "sixteen"
      Case 17: Buf = Buf & "seventeen"
      Case 18: Buf = Buf & "eighteen"
      Case 19: Buf = Buf & "nineteen"
   End Select

   EnglishDigitGroup = Buf
End Function
 
This is as per what mentioned in the post. As i don't kn ow anything about the code i just copy paste the text as it and changed the control name.

Please if you can modify in this as per my requirement then i can use the same code as it is. Also i need Rial and Baiza instead of Doller/Cents.

Please help/
 
Start by pasting *my* code in a new module and rename your existing procedure something else. If it compiles without error then see if it works in English.
 
Function i named as TextConvert. Now text box property is =TextConbert([Bill_Amount])
But getting result as #Name?

Compiling dose not shows any error in code. Also there is no other module available now.

Please suggest.
 
You must log in or register to reply here.

Similar threads

D
Solved Display Less Decimal Point Accuracy To What Is Stored In Calculated Field Of Query
Replies
7
Views
388
gemma-the-husky
gemma-the-husky
S
Bookmarks showing number in to many decimals
Replies
2
Views
207
silentwolf
S
J
Solved Column in Text Box
Replies
10
Views
480
JoséBob
J
T
Table Displayed in Text Box?
Replies
7
Views
363
TheSearcher
T
J
Display Selection of a List Box in a Text Box
2
Replies
29
Views
1,573
JithuAccess
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom