By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,231 Members | 1,636 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Converting numbers to Words

missinglinq
Expert 2.5K+
P: 3,532
From the Access Object Dialog Box click on "Modules"
Click on new to make a new module
Copy then paste the following code into the new module:

Expand|Select|Wrap|Line Numbers
  1. Function SayNo(ByVal N As Currency) As String
  2.  
  3.     Const Thousand = 1000@
  4.     Const Million = Thousand * Thousand
  5.     Const Billion = Thousand * Million
  6.     Const Trillion = Thousand * Billion
  7.  
  8.     If (N = 0@) Then SayNo = "zero": Exit Function
  9.  
  10.     Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
  11.     Dim Frac As Currency: Frac = Abs(N - Fix(N))
  12.     If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
  13.     Dim AtLeastOne As Integer: AtLeastOne = N >= 1
  14.  
  15.     If (N >= Trillion) Then
  16.         Debug.Print N
  17.         Buf = Buf & SayNoDigitGroup(Int(N / Trillion)) & " trillion"
  18.         N = N - Int(N / Trillion) * Trillion
  19.         If (N >= 1@) Then Buf = Buf & " "
  20.     End If
  21.  
  22.     If (N >= Billion) Then
  23.         Debug.Print N
  24.         Buf = Buf & SayNoDigitGroup(Int(N / Billion)) & " billion"
  25.         N = N - Int(N / Billion) * Billion
  26.         If (N >= 1@) Then Buf = Buf & " "
  27.     End If
  28.  
  29.     If (N >= Million) Then
  30.         Debug.Print N
  31.         Buf = Buf & SayNoDigitGroup(N \ Million) & " million"
  32.         N = N Mod Million
  33.         If (N >= 1@) Then Buf = Buf & " "
  34.     End If
  35.  
  36.     If (N >= Thousand) Then
  37.         Debug.Print N
  38.         Buf = Buf & SayNoDigitGroup(N \ Thousand) & " thousand"
  39.         N = N Mod Thousand
  40.         If (N >= 1@) Then Buf = Buf & " "
  41.     End If
  42.  
  43.     If (N >= 1@) Then
  44.         Debug.Print N
  45.         Buf = Buf & SayNoDigitGroup(N)
  46.     End If
  47.  
  48.     If (Frac = 0@) Then
  49.         Buf = Buf
  50.     ElseIf (Int(Frac * 100@) = Frac * 100@) Then
  51.         If AtLeastOne Then Buf = Buf & " and "
  52.         Buf = Buf & Format$(Frac * 100@, "00") & "/100"
  53.     Else
  54.         If AtLeastOne Then Buf = Buf & " and "
  55.         Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
  56.     End If
  57.  
  58.     SayNo = Buf
  59. End Function
  60.  
  61. Private Function SayNoDigitGroup(ByVal N As Integer) As String
  62.  
  63.     Const Hundred = " hundred"
  64.     Const One = "one"
  65.     Const Two = "two"
  66.     Const Three = "three"
  67.     Const Four = "four"
  68.     Const Five = "five"
  69.     Const Six = "six"
  70.     Const Seven = "seven"
  71.     Const Eight = "eight"
  72.     Const Nine = "nine"
  73.     Dim Buf As String: Buf = ""
  74.     Dim Flag As Integer: Flag = False
  75.  
  76.     Select Case (N \ 100)
  77.         Case 0: Buf = "": Flag = False
  78.         Case 1: Buf = One & Hundred: Flag = True
  79.         Case 2: Buf = Two & Hundred: Flag = True
  80.         Case 3: Buf = Three & Hundred: Flag = True
  81.         Case 4: Buf = Four & Hundred: Flag = True
  82.         Case 5: Buf = Five & Hundred: Flag = True
  83.         Case 6: Buf = Six & Hundred: Flag = True
  84.         Case 7: Buf = Seven & Hundred: Flag = True
  85.         Case 8: Buf = Eight & Hundred: Flag = True
  86.         Case 9: Buf = Nine & Hundred: Flag = True
  87.     End Select
  88.  
  89.     If (Flag <> False) Then N = N Mod 100
  90.     If (N > 0) Then
  91.         If (Flag <> False) Then Buf = Buf & " "
  92.     Else
  93.         SayNoDigitGroup = Buf
  94.         Exit Function
  95.     End If
  96.  
  97.     Select Case (N \ 10)
  98.         Case 0, 1: Flag = False
  99.         Case 2: Buf = Buf & "twenty": Flag = True
  100.         Case 3: Buf = Buf & "thirty": Flag = True
  101.         Case 4: Buf = Buf & "forty": Flag = True
  102.         Case 5: Buf = Buf & "fifty": Flag = True
  103.         Case 6: Buf = Buf & "sixty": Flag = True
  104.         Case 7: Buf = Buf & "seventy": Flag = True
  105.         Case 8: Buf = Buf & "eighty": Flag = True
  106.         Case 9: Buf = Buf & "ninety": Flag = True
  107.     End Select
  108.  
  109.     If (Flag <> False) Then N = N Mod 10
  110.     If (N > 0) Then
  111.         If (Flag <> False) Then Buf = Buf & "-"
  112.     Else
  113.         SayNoDigitGroup = Buf
  114.         Exit Function
  115.     End If
  116.  
  117.     Select Case (N)
  118.         Case 0:
  119.         Case 1: Buf = Buf & One
  120.         Case 2: Buf = Buf & Two
  121.         Case 3: Buf = Buf & Three
  122.         Case 4: Buf = Buf & Four
  123.         Case 5: Buf = Buf & Five
  124.         Case 6: Buf = Buf & Six
  125.         Case 7: Buf = Buf & Seven
  126.         Case 8: Buf = Buf & Eight
  127.         Case 9: Buf = Buf & Nine
  128.         Case 10: Buf = Buf & "ten"
  129.         Case 11: Buf = Buf & "eleven"
  130.         Case 12: Buf = Buf & "twelve"
  131.         Case 13: Buf = Buf & "thirteen"
  132.         Case 14: Buf = Buf & "fourteen"
  133.         Case 15: Buf = Buf & "fifteen"
  134.         Case 16: Buf = Buf & "sixteen"
  135.         Case 17: Buf = Buf & "seventeen"
  136.         Case 18: Buf = Buf & "eighteen"
  137.         Case 19: Buf = Buf & "nineteen"
  138.     End Select
  139.  
  140.     SayNoDigitGroup = Buf
  141.  
  142. End Function
  143.  
Save the module and call it, say, NumbersToWords

Now from your form call the function using the syntax

SayNo(YourNumericalValue)

As an example (since I don't know your skill level, sorry) here's what I did to test it. After doing the above to create the module, I ran up a simple form with a textbox and a label; txtTextNumbers and lblTextWords

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtTextNumbers_Exit(Cancel As Integer)
  2.      Me.lblTextWords.Caption = SayNo(Me.txtTextNumbers.Value)
  3. End Sub
  4.  
After entering your data and exiting the textbox the label will contain the word equivelent of the number entered.

The same code can be used in other events, of course.

The author is only known to me as M8KWR.
Jan 30 '07 #1
Share this Article
Share on Google+
1 Comment


puppydogbuddy
Expert 100+
P: 1,923