MS Access 2003: Convert currency into words
This MS Access tutorial explains how to set up a function to convert currency into words in Access 2003.
See solution in other versions of MSAccess:
Description
In Access 2003, you can take a numeric value and convert this value into the word representation of the number.
For example:
$100 should read as "one hundred exactly"
This conversion is done through a custom function called English that you will need to copy into your database. Once you've copied this function into your database, you use the English function whenever you want to convert currency into words.
Let's explore how to set up this function in Access 2003.
Instructions
To set up the English custom function, you will need to create a new module. To create a new module, select the Module tab in the Database Window.
Then click on the New button in the toolbar.
This should open the Microsoft Visual Basic editor and display the new module. In this example, you will see a new module called Module1 listed in the Project Manager window.
Paste the following two functions English and EnglishDigitGroup in the Code window in the VB editor window:
Function English(ByVal N As Currency) As String Const Thousand = 1000@ Const Million = Thousand * Thousand Const Billion = Thousand * Million Const Trillion = Thousand * Billion If (N = 0@) Then English = "zero": Exit Function Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = "" Dim Frac As Currency: Frac = Abs(N - Fix(N)) If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N)) Dim AtLeastOne As Integer: AtLeastOne = N >= 1 If (N >= Trillion) Then Debug.Print N Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion" N = N - Int(N / Trillion) * Trillion If (N >= 1@) Then Buf = Buf & " " End If If (N >= Billion) Then Debug.Print N Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion" N = N - Int(N / Billion) * Billion If (N >= 1@) Then Buf = Buf & " " End If If (N >= Million) Then Debug.Print N Buf = Buf & EnglishDigitGroup(N \ Million) & " million" N = N Mod Million If (N >= 1@) Then Buf = Buf & " " End If If (N >= Thousand) Then Debug.Print N Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand" N = N Mod Thousand If (N >= 1@) Then Buf = Buf & " " End If If (N >= 1@) Then Debug.Print N Buf = Buf & EnglishDigitGroup(N) End If If (Frac = 0@) Then Buf = Buf & " exactly" ElseIf (Int(Frac * 100@) = Frac * 100@) Then If AtLeastOne Then Buf = Buf & " and " Buf = Buf & Format$(Frac * 100@, "00") & "/100" Else If AtLeastOne Then Buf = Buf & " and " Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000" End If English = Buf End Function Private Function EnglishDigitGroup(ByVal N As Integer) As String Const Hundred = " hundred" Const One = "one" Const Two = "two" Const Three = "three" Const Four = "four" Const Five = "five" Const Six = "six" Const Seven = "seven" Const Eight = "eight" Const Nine = "nine" Dim Buf As String: Buf = "" Dim Flag As Integer: Flag = False Select Case (N \ 100) Case 0: Buf = "": Flag = False Case 1: Buf = One & Hundred: Flag = True Case 2: Buf = Two & Hundred: Flag = True Case 3: Buf = Three & Hundred: Flag = True Case 4: Buf = Four & Hundred: Flag = True Case 5: Buf = Five & Hundred: Flag = True Case 6: Buf = Six & Hundred: Flag = True Case 7: Buf = Seven & Hundred: Flag = True Case 8: Buf = Eight & Hundred: Flag = True Case 9: Buf = Nine & Hundred: Flag = True End Select If (Flag <> False) Then N = N Mod 100 If (N > 0) Then If (Flag <> False) Then Buf = Buf & " " Else EnglishDigitGroup = Buf Exit Function End If Select Case (N \ 10) Case 0, 1: Flag = False Case 2: Buf = Buf & "twenty": Flag = True Case 3: Buf = Buf & "thirty": Flag = True Case 4: Buf = Buf & "forty": Flag = True Case 5: Buf = Buf & "fifty": Flag = True Case 6: Buf = Buf & "sixty": Flag = True Case 7: Buf = Buf & "seventy": Flag = True Case 8: Buf = Buf & "eighty": Flag = True Case 9: Buf = Buf & "ninety": Flag = True End Select If (Flag <> False) Then N = N Mod 10 If (N > 0) Then If (Flag <> False) Then Buf = Buf & "-" Else EnglishDigitGroup = Buf Exit Function End If Select Case (N) Case 0: Case 1: Buf = Buf & One Case 2: Buf = Buf & Two Case 3: Buf = Buf & Three Case 4: Buf = Buf & Four Case 5: Buf = Buf & Five Case 6: Buf = Buf & Six Case 7: Buf = Buf & Seven Case 8: Buf = Buf & Eight Case 9: Buf = Buf & Nine Case 10: Buf = Buf & "ten" Case 11: Buf = Buf & "eleven" Case 12: Buf = Buf & "twelve" Case 13: Buf = Buf & "thirteen" Case 14: Buf = Buf & "fourteen" Case 15: Buf = Buf & "fifteen" Case 16: Buf = Buf & "sixteen" Case 17: Buf = Buf & "seventeen" Case 18: Buf = Buf & "eighteen" Case 19: Buf = Buf & "nineteen" End Select EnglishDigitGroup = Buf End Function
Your Code window should now look like this:
Click on the Save button in the toolbar.
A window should appear prompting you to select a name for the new module. In this example, we have entered the name modGeneral. Enter your name and click on the OK button.
You should see your new module called modGeneral appear in the Database Window. This is where the English function to convert currency into words has been saved.
Now, when you want to convert currency into words, you will reference the English function in your Access database as follows:
English (100) Result: "one hundred exactly" English (125) Result: "one hundred twenty-five exactly" English (125.3) Result: "one hundred twenty-five and 30/100"
Frequently Asked Questions
Question: How do I get this function to work in a form?
Answer: To use this function, first copy the two functions listed above (English and EnglishDigitGroup) into a Module in your Access database.
Then, open your form in Design view, create a new text box, and view the properties for the new text box. Select the Control Source property and enter the following formula:
=English([UnitPrice])
What this formula does is display the English representation of the field UnitPrice.
Now when you view your form, you should see the Unit Price information displayed in English in the last text box on the form.
Advertisements