ACC: How to Convert Currency or Numbers into English Words

This article was previously published under Q95640
This article has been archived. It is offered "as is" and will no longer be updated.
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article shows you how to create a sample, user-defined function namedConvertCurrencyToEnglish() to convert a numeric value to an English wordrepresentation. For example, the function will return the following wordsfor 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 forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout 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 MicrosoftAccess versions 1.x and 2.0. For more information about Access Basic,please refer to the "Introduction to Programming" manual in MicrosoftAccess version 1.x or the "Building Applications" manual in MicrosoftAccess version 2.0

NOTE: This article explains a technique demonstrated in the samplefiles, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0)and RptSmp97.exe (for Microsoft Access 97). For information about howto obtain these sample files, please see the following articles in theMicrosoft Knowledge Base:
145777 ACC95: Microsoft Access Sample Reports Available in Download Center
175072 ACC97: Microsoft Access 97 Sample Reports Available in Download Center
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.
how to check writing numbers words report printing checks change into turn into
Properties

Article ID: 95640 - Last Review: 12/04/2015 09:23:19 - Revision: 2.3

Microsoft Access 1.0 Standard Edition, Microsoft Access 1.1 Standard Edition, Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kbhowto kbprogramming KB95640
Feedback