This article shows you how to create a sample, user-defined function named
ConvertCurrencyToEnglish() 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:
ACC95: Microsoft Access Sample Reports Available in Download Center
ACC97: Microsoft Access 97 Sample Reports Available in Download Center
To create the ConvertCurrencyToEnglish() function, follow these steps:
- Create a new module and type the following line in the Declarations section if the line is not already there:
- Type the following four procedures:
Function ConvertCurrencyToEnglish (ByVal MyNumber)
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))
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)
MyNumber = ""
Count = Count + 1
' Clean up dollars.
Select Case Dollars
Dollars = "No Dollars"
Dollars = "One Dollar"
Dollars = Dollars & " Dollars"
' Clean up cents.
Select Case Cents
Cents = " And No Cents"
Cents = " And One Cent"
Cents = " And " & Cents & " Cents"
ConvertCurrencyToEnglish = Dollars & Cents
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 "
' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & ConvertTens(Mid(MyNumber, 2))
' If not, then convert the ones place digit.
Result = Result & ConvertDigit(Mid(MyNumber, 3))
ConvertHundreds = Trim(Result)
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"
' .. 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 "
' Convert ones place digit.
Result = Result & ConvertDigit(Right(MyTens, 1))
ConvertTens = Result
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 = ""
- 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:
How to Use the ConvertCurrencyToEnglish() Function on a Form
To demonstrate the use of the ConvertCurrencyToEnglish function on a form,
follow these steps:
- Open the sample database Northwind.mdb (or Nwind.mdb in versions 1.x and 2.0).
- Open the Orders form in Design view and add a text box to the form.
Set the text box's ControlSource property as follows:
- 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.
Article ID: 95640 - Last Review: January 18, 2007 - 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
|kbhowto kbprogramming KB95640|Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.