I want to print a replica of a check. The user will enter the amount in dollars and cents. How do I take that amount and convert it to words for the second line of the check. IE - the user enters 20.83 and the report shows "Twenty eighty-three".

Thanks, Roni

Insert the following code into a module within your database called Numbers to Words:

Option Compare Database 'Use database order for string comparisons
Option Explicit 'Require explicit variable declaration
'Variables used in NumWord() procedure defined here in Declarations.
Dim EngNum(90) As String
Dim StringNum As String, Chunk As String, English As String
Dim Hundreds As Integer, Tens As Integer, Ones As Integer
Dim LoopCount As Integer, StartVal As Integer
Dim TensDone As Integer
Dim Pennies As String
Static Function NumWord(ByVal AmountPassed As Currency) As String

'** Convert a number to words for filling in the Amount of a check
'** Example: NumWord(120.45) returns ONE HUNDRED TWENTY AND 45/100
'** Can handle numbers from 0 to $999,999.99
'** The array below, and other variables, are dimensioned
'** in the Declarations section.

'** Fill EngNum array, if it's not filled already)
If Not EngNum(1) = "One" Then
EngNum(0) = ""
EngNum(1) = "One"
EngNum(2) = "Two"
EngNum(3) = "Three"
EngNum(4) = "Four"
EngNum(5) = "Five"
EngNum(6) = "Six"
EngNum(7) = "Seven"
EngNum(8) = "Eight"
EngNum(9) = "Nine"
EngNum(10) = "Ten"
EngNum(11) = "Eleven"
EngNum(12) = "Twelve"
EngNum(13) = "Thirteen"
EngNum(14) = "Fourteen"
EngNum(15) = "Fifteen"
EngNum(16) = "Sixteen"
EngNum(17) = "Seventeen"
EngNum(18) = "Eighteen"
EngNum(19) = "Nineteen"
EngNum(20) = "Twenty"
EngNum(30) = "Thirty"
EngNum(40) = "Forty"
EngNum(50) = "Fifty"
EngNum(60) = "Sixty"
EngNum(70) = "Seventy"
EngNum(80) = "Eighty"
EngNum(90) = "Ninety"
End If


'** Convert incoming Currency value to a string for parsing.
StringNum = Format$(AmountPassed, "000000.00")

'** Initialize other variables
English = ""
LoopCount = 1
StartVal = 1
Pennies = Mid$(StringNum, 8, 2)

'** Just in case the check is for less than a buck...
If AmountPassed < 1 Then
English = "Zero"
End If

'** Now do each 3-digit section of number.
While LoopCount <= 2
Chunk = Mid$(StringNum, StartVal, 3)
Hundreds = Val(Mid$(Chunk, 1, 1))
Tens = Val(Mid$(Chunk, 2, 2))
Ones = Val(Mid$(Chunk, 3, 1))

'** Do the hundreds portion of 3-digit number
If Val(Chunk) > 99 Then
English = English & EngNum(Hundreds) & " Hundred "
End If

'** Do the tens & ones portion of 3-digit number
TensDone = False

'** Is it less than 10?
If Tens < 10 Then
English = English & " " & EngNum(Ones)
TensDone = True
End If

'** Is it a teen?
If (Tens >= 11 And Tens <= 19) Then
English = English & EngNum(Tens)
TensDone = True
End If

'** Is it Evenly Divisible by 10?
If (Tens / 10#) = Int(Tens / 10#) Then
English = English & EngNum(Tens)
TensDone = True
End If

'** Or is it none of the above?
If Not TensDone Then
English = English & EngNum((Int(Tens / 10)) * 10)
English = English & " " & EngNum(Ones)
End If

'** Add the word "thousand" if necessary.
If AmountPassed > 999.99 And LoopCount = 1 Then
English = English + " Thousand "
End If

'** Do pass through second three digits
LoopCount = LoopCount + 1
StartVal = 4
Wend
'** Done: Return english with pennies tacked on.
NumWord = Trim(English) & " and " & Pennies & "/100"

End Function

In your report, the following should be inserted into the control line where you want the number conversion to take place:

=NumWord([Amount])

I can't take the credit for this code, it originated from Alan Simpson.

Good Luck



[This message has been edited by Carol (edited 03-06-2000).]

I know this is an old message, but I need some help with it. I have always used Access 2.0 (it does some things for me I haven't figured out how to do in later versions).

At any rate, I had downloaded a module that I simply plugged in to convert numbers to words that I then used on a form to print my checks. Always worked like a charm.

For some reason, my back up of the database that I use has become corrupted (or when migrating to a new computer, I lost the module - perhaps that's the problem).

In any event, I can't get the old database to open up in my current installation of msaccess2.0.

There is nothing magic about that old database - I would readily create a new one, but can't get my numbers to convert to words.

Tried pasting the above code into a module, but still can't figure out how to make it work. I'm not programmer, so, what is obvious to you all is greek to me. Do I copy and paste everything in the message from 'Option compare' through and including 'End Function'?

After pasting into the module, when I close the module, Access prompts me to save it. Can I use Access' default 'module1' or should I call it something else?

Sorry to be asking what must be such obvious questions.

Thanks for any help you can give.

Caruso

After pasting into the module, when I close the module, Access prompts me to save it. Can I use Access' default 'module1' or should I call it something else?The answer is Yes to either question. Just *don't* name it NumWord.

Since you posted this in the Reports forum, I'll assume this is in a report. The underlying query/table for the report will probably have a field for the CheckAmount which is currency of some kind. Where you want the words printed just put a textbox with the ControlSource set to:
=NumWord([CheckAmount])

...using your field name for CheckAmount of course!

I must be doing something wrong. When I try your suggestion on a report I get this message:

Compile Error in 'On Unload' expression.

What have I done incorrectly?

Thanks for your previous reply and any additional help you can offer.

Caruso

Do you have some code in the UnLoad event?

All I did was copy the code and paste it into the module. Sorry, I don't know anything about an unload event. I am obviously inexperienced at this.

Thanks for anything suggestion you can offer to help me track down the trouble.

Caruso

I got the feeling you pasted the code in a standard module. If so then open that module and go to Debug>Compile. The compiler should highlight a line it does not like. Post that line here so we can look at it.

I could not find the exact menu choices "compile">"debug", bit did find a choice to "compile loaded modules" under the Run menu. I did that. At this point in the code:

If AmountPassed < 1 Then

the semicolon was highlighted. The message that pops up says:

Expected: Then

If I click help, I see an explanation that states that the error is usually to the left of the highlighted text, so I'll try typing a 'then' left of the semicolon to see what happens (can't hurt to try, right?).

Let me know what the above tells you.

Thanks again for your help.

Caruso

You probably already know that typing a 'then' to the left of the semicolon would not solve the problem. Let me know what I need to do.
Thanks.
Caruso

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

Ok, numbers to pounds and pence works.
Please, oh, please, help me to get numbers to dollars and cents.
Pretty please.
Carusoswi

Ok, numbers to pounds and pence works.
Please, oh, please, help me to get numbers to dollars and cents.
Pretty please.
Carusoswi

Change Pounds to Dollars in the code and Pence to Cents...

now, I'm trying to figure out how to not spell out the cents. I figured out how to alter the code so that 10.00 will result in 'ten and 00/100' and I can get this result by altering the code 10.53-->ten and fifty three/100, but, I have yet to figure out how to get 'ten and 53/100.


It is fascinating, however. I'm going to keep trying. . . . very interesting to see what minor changes in the code do to the output. I tried to get this -->ten and -----53/100. I changed all the "numtext" to digits and got this --> --10 and ---53/100.

Obviously, all the conversion from numbers to text is controlled by the same section of code. Interesting.

If you can offer me additional pointers, they would be appreciated. Also, where might I look to get a deeper understainding of the workings of this code? I'd love to be able to understand how each entry and each section of entries adds to the function of this entire module.

Again, thanks for your assistance thus far.

Caruso

Hey Guys,

Currency to Word doesn't work at all. It displays " #Name??# ", What's wrong here. Could anyone help me. I'm using Access 2003, db file in ver. 2000.

Thanks,
David

Did you put all of Rich's code in a standard module? What line of code is throwing the error?