nubers total to words

Function ConvertCurrencyToEnglish(ByVal MyNumber)
Dim Temp
Dim Pounds, Pence
Dim DecimalPlace, count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))

' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")

' If we find decimal place...
If DecimalPlace > 0 Then
' Convert cents
Temp = left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Pence = ConvertTens(Temp)

' Strip off cents from remainder to convert.
MyNumber = Trim(left(MyNumber, DecimalPlace - 1))
End If

count = 1
Do While MyNumber <> ""
' Convert last 3 digits of MyNumber to English dollars.
Temp = ConvertHundreds(right(MyNumber, 3))
If Temp <> "" Then Pounds = Temp & Place(count) & Pounds
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
count = count + 1
Loop

' Clean up dollars.
Select Case Pounds
Case ""
Pounds = "No Pounds"
Case "One"
Pounds = "One Pound"
Case Else
Pounds = Pounds & " Pounds "
End Select

' Clean up cents.
Select Case Pence
Case ""
Pence = " Only"
Case "One"
Pence = " And One Pence"
Case Else
Pence = " And " & Pence & " Pence"
End Select

ConvertCurrencyToEnglish = Pounds & Pence
End Function


Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select

End Function

Private Function ConvertHundreds(ByVal MyNumber)
Dim result As String

' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function

' Append leading zeros to number.
MyNumber = right("000" & MyNumber, 3)

' Do we have a hundreds place digit to convert?
If left(MyNumber, 1) <> "0" Then
result = ConvertDigit(left(MyNumber, 1)) & " Hundred And "
End If

' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
result = result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
result = result & ConvertDigit(Mid(MyNumber, 3))
End If

ConvertHundreds = Trim(result)
End Function


Private Function ConvertTens(ByVal MyTens)
Dim result As String

' Is value between 10 and 19?
If Val(left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
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
' .. otherwise it's between 20 and 99.
Select Case Val(left(MyTens, 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

' Convert ones place digit.
result = result & ConvertDigit(right(MyTens, 1))
End If

ConvertTens = result
End Function
Change the instances of pounds and pence to Dollars and cents
 
Rich,

Have stepped through this and think it is absolute Poetry... nice work ;-)

J
 
Jibbadiah said:
Rich,

Have stepped through this and think it is absolute Poetry... nice work ;-)

J

So you tried it on reports and it works? What access version do you use?
 
Jibbadiah said:
Rich,

Have stepped through this and think it is absolute Poetry... nice work ;-)

J
I wish I could claim the credit but it's not mine and I've long forgotten who wrote the code originally:o ;)
 
Will have to remove the folloing comment from my copy...

Code:
'Credit to Rich, Access-Programmers.co.uk

:eek:

Sorry mate. :p
 
Ok... you caught me in a good mood... will do!! It's a beautifully crafted piece of code, so it's only fitting that someone takes some credit.

Good job matey ;)
 
I'm having trouble implementing the code above, could someone edit my attached db example to output report total in words.
 

Attachments

Smile, I can't help you with anything but Access 97... sorry.
 
Jibbadiah said:
Smile, I can't help you with anything but Access 97... sorry.

Then can you perhaps post some database example where numbers to words is used in a report
 
Take a look at this... kept it simple for you. Just open the report and you should get what you want.

J.
 

Attachments

Jibbadiah said:
Take a look at this... kept it simple for you. Just open the report and you should get what you want.

J.

Thanks for your example, It works fine, however I'll have to update some of the code to make it in my local currency.
 
smile said:
Thanks for your example, It works fine, however I'll have to update some of the code to make it in my local currency.

I changed the module to my national currency but how to change encoding?
I need UFT8 because when I type some symbols to thecode they are not right?
 
smile said:
I changed the module to my national currency but how to change encoding?
I need UFT8 because when I type some symbols to thecode they are not right?

Solved it myself, had to change in control panel -> regiona land language options -> advanced. You select your language and works fine
 

Users who are viewing this thread

Back
Top Bottom