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! how to convert numbers to words

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”)

how to convert numbers to words - queryNow run your query and the VBA function will handle and return any real numbers into text form using the ‘Order Amount‘ field:how to convert numbers to wordsI 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.

To understand the code will be down to your own knowledge of VBA code but I can help you learn how to programme in Access and how to convert numbers to words function works.

This entry was posted in Functions, Modules, MS Access, VBA and tagged , , , , . Bookmark the permalink.

5 Responses to How To Convert Numbers To Words: Access VBA Function

  1. 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″)

  2. 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 .

  3. Ralph Lante says:

    Thanks, a very handy function.

  4. Ben says:

    Thanks for sharing and happy to point visitors to your website :)

  5. 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))
    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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.