MS Access 2007: Convert Bangladesh taka currency into words
This MS Access tutorial explains how to set up a function to convert Bangladesh taka currency into words in Access 2007.
Description
In Access 2007, you can take a Bangladesh taka currency value and convert this value into the word representation of the number.
For example:
100 = one hundred taka 1000 = one thousand taka 1000.40 = one thousand forty paisa 1,00,000 = one lakh taka 1,00,00,000 = one koti taka
This conversion is done through a custom function called BangladeshTaka that you will need to copy into your database. Once you've copied this function into your database, you use the BangladeshTaka function whenever you want to convert Bangladesh taka currency into words.
Let's explore how to set up this function in Access 2007.
Instructions
To set up the BangladeshTaka custom function, you will need to create a new module. To create a new module, select the Create tab in the toolbar at the top of the screen.
Then select Module > Module in the Other group.
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 BangladeshTaka(ByVal N As Currency) As String Const Thousand = 1000@ Const Lakh = 100000@ Const Koti = 10000000@ If (N = 0@) Then BangladeshTaka = "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 >= Koti) Then Buf = Buf & BangladeshTakaDigitGroup(Int(N / Koti)) & " koti taka" N = N - Int(N / Koti) * Koti If (N >= 1@) Then Buf = Buf & " " End If If (N >= Lakh) Then Buf = Buf & BangladeshTakaDigitGroup(Int(N / Lakh)) & " lakh taka" N = N - Int(N / Lakh) * Lakh If (N >= 1@) Then Buf = Buf & " " End If If (N >= Thousand) Then Buf = Buf & BangladeshTakaDigitGroup(N \ Thousand) & " thousand taka" N = N Mod Thousand If (N >= 1@) Then Buf = Buf & " " End If If (N >= 1@) Then Buf = Buf & BangladeshTakaDigitGroup(N) & " taka" End If If (Frac = 0@) Then Buf = Buf & " " Else If AtLeastOne Then Buf = Buf & " " Buf = Buf & BangladeshTakaDigitGroup(Frac * 100) & " paisa" End If BangladeshTaka = Buf End Function Private Function BangladeshTakaDigitGroup(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 BangladeshTakaDigitGroup = 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 BangladeshTakaDigitGroup = 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 BangladeshTakaDigitGroup = 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 Navigation Pane. 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:
BangladeshTaka (100) Result: "one hundred taka" BangladeshTaka (1000) Result: "one thousand taka" BangladeshTaka (1000.40) Result: "one thousand forty paisa" BangladeshTaka (100000) Result: "one lakh taka" BangladeshTaka (10000000) Result: "one koti taka"
Troubleshooting Tip
If you have followed the instructions above and you get a #Name? error when attempting to use the English function, follow these instructions:
- Shut down and reopen the database file.
- When the database opens, click on the Options button next to the Security Warning.
- When the Microsoft Office Security Options window appear, select the option called Enable this Content.
- Click on the OK button.
Now the new VBA code can be run by the database. Before, it was being blocked by Microsoft Access which is why the function name was not recognized.
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 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:
=BangladeshTaka([Price])
What this formula does is display the word representation of the Bangladesh taka currency field called Price.
Now when you view your form, you should see the Price information displayed in taka in the last text box on this form.
Advertisements