INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

Function to translate numbers (like 100) to words (like "one hundred") by JimConrad
Posted: 13 Jun 00


Function NumberToWords(InputNumber As Double, Optional NeedCents As Boolean = False) As Variant

    Dim varWords        As Variant
    Dim Billions        As Double
    Dim Millions        As Double
    Dim Thousands       As Double
    Dim Hundreds        As Double
    Dim Cents           As Integer
    Dim varNumber       As Variant
    
    Dim OneBillion      As Double
    
    If Not IsNumeric(InputNumber) Then
        NumberToWords = Null
        Exit Function
    End If
     
    If InputNumber = 0 Then
        NumberToWords = "Zero "
        Exit Function
    End If

    Cents = (InputNumber - Int(InputNumber)) * 100
      
    InputNumber = Int(InputNumber)
    
    OneBillion = 1000000000

    Billions = Int(InputNumber / OneBillion)
    
    Millions = InputNumber - (Billions * OneBillion)
    Millions = Int(Millions / 1000000)
    
    Thousands = InputNumber - (Billions * OneBillion) - (Millions * 1000000)
    Thousands = Int(Thousands / 1000)
    
    Hundreds = InputNumber - (Billions * OneBillion) - (Millions * 1000000) - (Thousands * 1000)
    
    varWords = SmallNumberToWords(Billions) + "Billion, " & _
               SmallNumberToWords(Millions) + "Million, " & _
               SmallNumberToWords(Thousands) + "Thousand, " & _
               SmallNumberToWords(Hundreds)
               
    If NeedCents Then
        varWords = varWords & "And " & IIf(Cents < 10, "0" & Cents, Cents) & "/100"
    End If
    
    NumberToWords = varWords
    
End Function

Private Function SmallNumberToWords(SmallNumber As Double) As Variant

    Dim Hundreds        As Integer
    Dim Tens            As Integer
    Dim Units           As Integer
    Dim HundredsWords   As Variant
    Dim TensWords       As Variant
    Dim UnitsWords      As Variant
    
    Hundreds = Int(SmallNumber / 100)
    Tens = SmallNumber - (Hundreds * 100)   ' Part 1 - Get rid of hundreds
    Tens = Int(Tens / 10) * 10              ' Part 2 - Truncate Units
    Units = (SmallNumber - (Hundreds * 100)) - Tens
    
    If Tens <= 19 Then
        Tens = Tens + Units
        Units = 0
    End If
    
    Select Case Hundreds
        Case 1: HundredsWords = "One Hundred "
        Case 2: HundredsWords = "Two Hundred "
        Case 3: HundredsWords = "Three Hundred "
        Case 4: HundredsWords = "Four Hundred "
        Case 5: HundredsWords = "Five Hundred "
        Case 6: HundredsWords = "Six Hundred "
        Case 7: HundredsWords = "Seven Hundred "
        Case 8: HundredsWords = "Eight Hundred "
        Case 9: HundredsWords = "Nine Hundred "
        Case Else: HundredsWords = Null
    End Select
    
    Select Case Tens
        Case 1: TensWords = "One "
        Case 2: TensWords = "Two "
        Case 3: TensWords = "Three "
        Case 4: TensWords = "Four "
        Case 5: TensWords = "Five "
        Case 6: TensWords = "Six "
        Case 7: TensWords = "Seven "
        Case 8: TensWords = "Eight "
        Case 9: TensWords = "Nine "
        Case 10: TensWords = "Ten "
        Case 11: TensWords = "Eleven "
        Case 12: TensWords = "Twelve "
        Case 13: TensWords = "Thirteen "
        Case 14: TensWords = "Fourteen "
        Case 15: TensWords = "Fifteen "
        Case 16: TensWords = "Sixteen "
        Case 17: TensWords = "Seventeen "
        Case 18: TensWords = "Eighteen "
        Case 19: TensWords = "Nineteen "
        Case 20: TensWords = "Twenty "
        Case 30: TensWords = "Thirty "
        Case 40: TensWords = "Forty "
        Case 50: TensWords = "Fifty "
        Case 60: TensWords = "Sixty "
        Case 70: TensWords = "Seventy "
        Case 80: TensWords = "Eighty "
        Case 90: TensWords = "Ninety "
        Case Else: TensWords = Null
    End Select
    
    Select Case Units
        Case 1: UnitsWords = "One "
        Case 2: UnitsWords = "Two "
        Case 3: UnitsWords = "Three "
        Case 4: UnitsWords = "Four "
        Case 5: UnitsWords = "Five "
        Case 6: UnitsWords = "Six "
        Case 7: UnitsWords = "Seven "
        Case 8: UnitsWords = "Eight "
        Case 9: UnitsWords = "Nine "
        Case Else: UnitsWords = Null
    End Select
    
    'Debug.Print "Hundreds " & Hundreds; HundredsWords
    'Debug.Print "Tens " & Tens; TensWords
    'Debug.Print "Ones " & Units; UnitsWords
    
    SmallNumberToWords = HundredsWords & TensWords & UnitsWords
        
End Function


Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close