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.
Tags: access vba, access vba code example, custom vba function, how create a vba function, how to convert numbers to text, using vba code in ms access
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”)
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!
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.
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.
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.
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
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.
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.
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
Thank you so much… the “GTQ” was the answer… I had put GTQ with out the quotes.
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.
Not quite sure what this is supposed to do? Have you checked the correct syntax – it looks strange when calling a function.
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.
This code works….
Dim MyCurrencyCode As String
MyCurrencyCode = DLookup(“[currencyCode]”, “exchangerate”, “[CountryID] = ” & Me.CountryID)
MsgBox SpellNumber(100, MyCurrencyCode)