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

Now run your query and the VBA function will handle and return any real numbers into text form using the ‘*Order Amount*‘ field: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.

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

Thanks for sharing and happy to point visitors to your website ðŸ™‚

Thanks, a very handy function.

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 .

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