ACC: How to Convert Currency or Numbers into English Words

Last reviewed: October 24, 1997
Article ID: Q95640
The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes a sample, user-defined function named ConvertCurrencyToEnglish() that you can use to convert a numeric value to an English word representation. For example, the function will return the following words for the number 1234.56:

   One Thousand Two Hundred Thirty Four Dollars And Fifty Six Cents

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

NOTE: This article explains a technique demonstrated in the sample files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q145777
   TITLE     : ACC95: Microsoft Access Sample Reports Available on MSL

   ARTICLE-ID: Q175072
   TITLE     : ACC97: Microsoft Access 97 Sample Reports Available on MSL

MORE INFORMATION

To create the ConvertCurrencyToEnglish() function, follow these steps:

  1. Create a new module and type the following line in the Declarations section if the line is not already there:

          Option Explicit
    

  2. Type the following four procedures:

          Function ConvertCurrencyToEnglish (ByVal MyNumber)
    
             Dim Temp
             Dim Dollars, Cents
             Dim DecimalPlace, Count
    
             ReDim Place(9) As String
             Place(2) = " Thousand "
             Place(3) = " Million "
             Place(4) = " Billion "
             Place(5) = " Trillion "
    
             ' Convert MyNumber to a string, trimming extra spaces.
             MyNumber = Trim(Str(MyNumber))
    
             ' Find decimal place.
             DecimalPlace = InStr(MyNumber, ".")
    
             ' If we find decimal place...
             If DecimalPlace > 0 Then
                ' Convert cents
                Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
                Cents = ConvertTens(Temp)
    
                ' Strip off cents from remainder to convert.
                MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
             End If
    
             Count = 1
             Do While MyNumber <> ""
                ' Convert last 3 digits of MyNumber to English dollars.
                Temp = ConvertHundreds(Right(MyNumber, 3))
                If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
                If Len(MyNumber) > 3 Then
                   ' Remove last 3 converted digits from MyNumber.
                   MyNumber = Left(MyNumber, Len(MyNumber) - 3)
                Else
                   MyNumber = ""
                End If
                Count = Count + 1
             Loop
    
             ' Clean up dollars.
             Select Case Dollars
                Case ""
                   Dollars = "No Dollars"
                Case "One"
                   Dollars = "One Dollar"
                Case Else
                   Dollars = Dollars & " Dollars"
             End Select
    
             ' Clean up cents.
             Select Case Cents
                Case ""
                   Cents = " And No Cents"
                Case "One"
                   Cents = " And One Cent"
                Case Else
                   Cents = " And " & Cents & " Cents"
             End Select
    
             ConvertCurrencyToEnglish = Dollars & Cents
          End Function
    
          Private Function ConvertHundreds (ByVal MyNumber)
             Dim Result As String
    
             ' Exit if there is nothing to convert.
             If Val(MyNumber) = 0 Then Exit Function
    
             ' Append leading zeros to number.
             MyNumber = Right("000" & MyNumber, 3)
    
             ' Do we have a hundreds place digit to convert?
             If Left(MyNumber, 1) <> "0" Then
                Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
             End If
    
             ' Do we have a tens place digit to convert?
             If Mid(MyNumber, 2, 1) <> "0" Then
                Result = Result & ConvertTens(Mid(MyNumber, 2))
             Else
                ' If not, then convert the ones place digit.
                Result = Result & ConvertDigit(Mid(MyNumber, 3))
             End If
    
             ConvertHundreds = Trim(Result)
          End Function
    
          Private Function ConvertTens (ByVal MyTens)
             Dim Result As String
    
             ' Is value between 10 and 19?
             If Val(Left(MyTens, 1)) = 1 Then
                Select Case Val(MyTens)
                   Case 10: Result = "Ten"
                   Case 11: Result = "Eleven"
                   Case 12: Result = "Twelve"
                   Case 13: Result = "Thirteen"
                   Case 14: Result = "Fourteen"
                   Case 15: Result = "Fifteen"
                   Case 16: Result = "Sixteen"
                   Case 17: Result = "Seventeen"
                   Case 18: Result = "Eighteen"
                   Case 19: Result = "Nineteen"
                   Case Else
                End Select
             Else
                ' .. otherwise it's between 20 and 99.
                Select Case Val(Left(MyTens, 1))
                   Case 2: Result = "Twenty "
                   Case 3: Result = "Thirty "
                   Case 4: Result = "Forty "
                   Case 5: Result = "Fifty "
                   Case 6: Result = "Sixty "
                   Case 7: Result = "Seventy "
                   Case 8: Result = "Eighty "
                   Case 9: Result = "Ninety "
                   Case Else
                End Select
    
                ' Convert ones place digit.
                Result = Result & ConvertDigit(Right(MyTens, 1))
             End If
    
             ConvertTens = Result
          End Function
    
          Private Function ConvertDigit (ByVal MyDigit)
             Select Case Val(MyDigit)
                Case 1: ConvertDigit = "One"
                Case 2: ConvertDigit = "Two"
                Case 3: ConvertDigit = "Three"
                Case 4: ConvertDigit = "Four"
                Case 5: ConvertDigit = "Five"
                Case 6: ConvertDigit = "Six"
                Case 7: ConvertDigit = "Seven"
                Case 8: ConvertDigit = "Eight"
                Case 9: ConvertDigit = "Nine"
                Case Else: ConvertDigit = ""
             End Select
          End Function
    
    

  3. To test this function, type the following line in the Debug window (or Immediate window in version 1.x and 2.0), and then press ENTER:

          ? ConvertCurrencyToEnglish(1234.56)
    

How to Use the ConvertCurrencyToEnglish() Function on a Form

To demonstrate the use of the ConvertCurrencyToEnglish function on a form, follow these steps:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0).

  2. Open the Orders form in Design view and add a text box to the form. Set the text box's ControlSource property as follows:

          =ConvertCurrencyToEnglish([Total])
    

  3. Open the Orders form in Form view. Note that the text box displays an English word representation of the total order amount computed in the form's Total field.


Additional query words: how to check writing numbers to words report check
printing
Keywords : kbprg
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.