---

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:

1. Shut down and reopen the database file.
2. When the database opens, click on the Options button next to the Security Warning.
3. When the Microsoft Office Security Options window appear, select the option called Enable this Content.
4. 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.