fbpx
Logo
Mail us
ben@accessdatabasetutorial.com
accessdatabasetutorial
Home » Functions » How To Convert Numbers To Words: Access VBA Function

How To Convert Numbers To Words: Access VBA Function

How To Convert Numbers To Words: Access VBA Function


Once again, I’ve been asked by a client to create procedure to turn a monetary number (numerical value) into its text equivalent and so I wanted to share with you how to convert numbers to words using Visual Basic code that can also be applied not just to your Access database but with any application that entertains VBA code!

Access VBA

How To Convert Numbers To Words: The Steps

This article is intended for users who know and have some VBA code knowledge but need a little push to see how an Access function can be called into your database application via a query, form or report to name a few objects.

Start by creating a new module in your visual basic editor screen or via the new module section from your Navigation pane window and save it with a useful name like ‘mod_MyFunctions‘.

Now to save time working out how to create the code and their logical workflows, you may wish to copy the SpellNumber VBA function.

Compile and save your module and you are now ready to use the custom function.

For example, I could create a query to express the ‘Order Amount‘ values into a text narrative by calling the ‘SpellNumber‘ function.

In my query below, I have added a calculated field next to ‘Order Amount‘ which calls the SpellNumber function with the following arguments completed:

Spell The Order Amount: SpellNumber([Order Amount],”GBP”)

Access VBA

Now run your query and the VBA function will handle and return any real numbers into text form using the ‘Order Amount‘ field:

Access VBA

I created some flexibility where you can specify a currency of your choice by adding three commonly used country codes (GBP, USD and EUR). Of course, you can modify the code to add your own additional currencies too.

Tags: , , , , ,

18 Responses so far.

  1. Access Guru says:

    This is nice article to convert the no to string value. I have code which converts the currency into words in Microsoft Access using VBA.
    here is code:
    This is the function which return the 100 digit values>>
    Function GetHundreds(ByVal MyNumber)
    Dim result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right(“000” & MyNumber, 3)
    ‘ Convert the hundreds place.
    If Mid(MyNumber, 1, 1) “0” Then
    result = GetDigit(Mid(MyNumber, 1, 1)) & ” Hundred ”
    End If
    ‘ Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) “0” Then
    result = result & GetTens(Mid(MyNumber, 2))
    Else
    result = result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = result
    End Function
    ————————

    for get 10 digit values

    Function GetTens(TensText)
    Dim result As String
    result = “” ‘ Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then ‘ If value between 10-19…
    Select Case Val(TensText)
    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 ‘ If value between 20-99…
    Select Case Val(Left(TensText, 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
    result = result & GetDigit _ (Right(TensText, 1)) ‘ Retrieve ones place.
    End If
    GetTens = result
    End Function

    ——————-

    function for 1-9

    Function GetDigit(Digit)
    Select Case Val(Digit)
    Case 1: GetDigit = “One”
    Case 2: GetDigit = “Two”
    Case 3: GetDigit = “Three”
    Case 4: GetDigit = “Four”
    Case 5: GetDigit = “Five”
    Case 6: GetDigit = “Six”
    Case 7: GetDigit = “Seven”
    Case 8: GetDigit = “Eight”
    Case 9: GetDigit = “Nine”
    Case Else: GetDigit = “”
    End Select
    End Function

    ……………………..
    these function converts the number to string value.
    for to get whole utility code and for step by step process
    http://accessguru.net/Articles_MSAccess/0048-Convert%20currency%20into%20words%20in%20Microsoft%20Access%20using%20VBA.php

  2. Ralph Lante says:

    Thanks, a very handy function.

  3. kpnaidu says:

    suppose a number 426.368 is to be converted as Four Two six point three six eight.
    what is the code to convert in ms access.please help .

    • Ben says:

      You could something…

      Function GetDigits(s) As String

      Dim point As Integer, i As Integer
      Dim sAnswer As String

      point = InStr(1, s, “.”, vbTextCompare)

      For i = 1 To Len(s)

      If i = point Then sAnswer = sAnswer & ” point” & GetDigit(Mid(s, i, 1))

      sAnswer = sAnswer & ” ” & GetDigit(Mid(s, i, 1))

      Next i

      GetDigits = Trim(sAnswer)

      End Function

      and then call the function GetDigits(“426.368”)

  4. Darrell Childress says:

    Just wanted to say THANKS for this. Copied the code into a module, used it in a query, works exactly as needed, very thankful for folks like you who post helpful information that helps out code-poor folks like me!

  5. Roger says:

    This is awesome code. I have implemented and it works but I can’t get the selection for different Currencies to work. I tried to use this TextDollarAmount = SpellNumber(CheckAmount, USD). It works but in Pounds and Pence not USD.

    • benadt2017 says:

      Make sure you have checked all the code. The last procedures handles the currency and it all works fine. Remember the optional argument where it defaults to ‘GDBP’ that can be omitted so it uses the multi currency list.

  6. Roger says:

    Thank you for your reply.
    I changed Function SpellNumber(ByVal MyNumber, _
    Optional ByVal MyCurrency As String = “GBP”) As String ===>
    Function SpellNumber(ByVal MyNumber, _
    Optional ByVal MyCurrency As String) As String

    I am using TextDollarAmount = SpellNumber(CheckAmount, GTQ) to write the text.
    And I have it defined below as…
    Select Case UCase(CountryCode)
    Case Is = “GTQ”
    If b Then
    GetCurrencyNarrative = “Quetzales”
    Else
    GetCurrencyNarrative = “Centavos”
    End If
    Case Is = “USD”
    If b Then
    GetCurrencyNarrative = “Dollars”
    Else
    GetCurrencyNarrative = “Cents”

    It is still defaulting to Pounds….. I know I can change that below but I actually have need to use USD as well as GTQ so this is why I am trying to get this to work.
    Thanks again.

  7. Roger says:

    I tried to delete the GBP code that you mentioned
    Function SpellNumber(ByVal MyNumber, _
    Optional ByVal MyCurrency As String) As String

    I can’t get the GTQ function to work or the USD for that matter.
    Select Case UCase(CountryCode)
    Case Is = “GTQ”
    If b Then
    GetCurrencyNarrative = “Quetzales”
    Else
    GetCurrencyNarrative = “Centavos”
    End If
    Case Is = “USD”
    If b Then
    GetCurrencyNarrative = “Dollars”
    Else
    GetCurrencyNarrative = “Cents”
    End If

  8. benadt2017 says:

    Check the GetCurrencyNarrative function where the default (the catch for an error) is “GBP”….
    The end of code looks like…
    Case Else ‘catch if the above is not working – default to…
    If b Then
    GetCurrencyNarrative = “Pounds”
    Else
    GetCurrencyNarrative = “Pence”
    End If
    End Select

    And remove this part to see if it causes an error because this is what’s happening… there’s an error somewhere and without seeing the full use of all procedures you are using to get this function to work, is difficult to pin point.

  9. Roger says:

    That didn’t work. I have tried to remove the GBP and I also removed the default case.. Now I have no Pounds, Pence.
    I have been reading through the code and I can’t find references to countrycode.
    I love the Script, I just can’t get the Multi currency to work.

    • benadt2017 says:

      Hi,
      I’ve checked this code and used your currency example and it worked for me!
      Make sure if you have copied the VBA code from this website page that you may have to physically retype the double quotation marks (in all places) as it doesn’t always seem to paste as a valid character.

      I added this code to GetCurrencyNarrative function…

      Select Case UCase(CountryCode)
      Case Is = “GTQ”
      If b Then
      GetCurrencyNarrative = “Quetzales”
      Else
      GetCurrencyNarrative = “Centavos”
      End If
      ….

      And it worked as expected.

      Please confirm where you are calling this function and the function you are using. I used a text box on a form with the Control source set as =SpellNumber([Text2],”GTQ”) … where [Text2] is the value being converted.
      Ben

  10. Roger says:

    Thank you so much… the “GTQ” was the answer… I had put GTQ with out the quotes.

  11. Roger says:

    I have a follow up question. I am wanting to lookup the country code “GTQ” or “HNL”.. I can’t make the “s come in to the string. I am trying this.
    MyCurrencyCode = DLookup(“currencyCode”, “exchangerate”, “CountryID=” & Me.CountryID)
    MsgBox MyCurrencyCode
    SpellCode = Chr(34) & MyCurrencyCode & Chr(34)
    MsgBox SpellCode
    TextDollarAmount = SpellNumber(CheckAmount, “SpellCode”)
    End Sub
    Yes I know there are some duplicates… I have tried it from every angle I can think of… even changing the text in the ExchangeRate to include the “s” around the Code… Any ideas would be appreciated.

  12. Ben says:

    Not quite sure what this is supposed to do? Have you checked the correct syntax – it looks strange when calling a function.

  13. Roger says:

    I just want it to lookup the current MyCurrencyCode = DLookup(“currencyCode”, “exchangerate”, “CountryID=” & Me.CountryID) and then pass that to the TextDollarAmount = SpellNumber(CheckAmount, MyCurrencyCode) Spell Number… The problem is I can’t get it to work. If I put it like this
    Dim strVar As String
    strVar = “GTQ”
    TextDollarAmount = SpellNumber(CheckAmount, strVar) it will work but Not when I lookup the value… Been playing with it for a couple days and I can’t figure it out.

    • benadt2017 says:

      This code works….
      Dim MyCurrencyCode As String
      MyCurrencyCode = DLookup(“[currencyCode]”, “exchangerate”, “[CountryID] = ” & Me.CountryID)
      MsgBox SpellNumber(100, MyCurrencyCode)

Leave a Reply

Your email address will not be published. Required fields are marked *