Numbers To Words

[Derrick T. Davidson]

Here is what I have done (I am no expert): 1) I created a module using code from the Web called NumWord 2) I have a report which has a total field [AccessTotalsCount] 3) I added a new text box 4) I put in the new text box properties On click =NumWords ( [AccessTotalsCount] ) 5) When I do this I get the message "The expression you entered has a function containing the wrong number of arguments" Apologies if this is a dumb question but I am unsure how to use the module.......

[June7]

Post your code and/or link to the reference you used.

You created NumWord as a public function in a general module?

Call the function from textbox ControlSource property, not the Click event.

[Derrick T. Davidson]

I have been playing with it and I put in the control source =NumWords ( [AccessTotalsCount], ) after the comma it asks for "intLength" ............ in the code I copied it says "intLength - Length of string to create (pads with trailing asterisks to fill to length)." what is a pad?

[Derrick T. Davidson]

This is the code I used.............................................. .......................... Option Compare Database
Function NumWord(ByVal dblAmount As Double, intLength As Integer) As String
' Comments : Converts a number to (English) text with padding/length options (for check writing)
' Parameters: dblAmount - Dollar amount to convert
' intLength - Length of string to create (pads with trailing asterisks to fill to length).
' If text exceeds the given length, the numeric representation is given.
' If length is 0, the full string is provided with no padding.
' Returns : string representation of the amount
Dim strText As String
Dim strCurrFormat As String
Dim intLowDigit As Integer
Dim strCents As String
Dim intGroup As Integer
Dim intDigit1 As Integer
Dim intDigit2 As Integer
Dim intDigit3 As Integer
Dim strSubText As String
Dim arrGroupIdx As Integer
Dim intCounter As Integer
ReDim arrOnes(1 To 19) As String
ReDim arrTens(1 To 9) As String
ReDim arrGroup(1 To 3) As String
arrOnes(1) = "ONE"
arrOnes(2) = "TWO"
arrOnes(3) = "THREE"
arrOnes(4) = "FOUR"
arrOnes(5) = "FIVE"
arrOnes(6) = "SIX"
arrOnes(7) = "SEVEN"
arrOnes(8) = "EIGHT"
arrOnes(9) = "NINE"
arrOnes(10) = "TEN"
arrOnes(11) = "ELEVEN"
arrOnes(12) = "TWELVE"
arrOnes(13) = "THIRTEEN"
arrOnes(14) = "FOURTEEN"
arrOnes(15) = "FIFTEEN"
arrOnes(16) = "SIXTEEN"
arrOnes(17) = "SEVENTEEN"
arrOnes(18) = "EIGHTTEEN"
arrOnes(19) = "NINETEEN"
arrTens(1) = "TEN"
arrTens(2) = "TWENTY"
arrTens(3) = "THIRTY"
arrTens(4) = "FORTY"
arrTens(5) = "FIFTY"
arrTens(6) = "SIXTY"
arrTens(7) = "SEVENTY"
arrTens(8) = "EIGHTY"
arrTens(9) = "NINETY"
arrGroup(1) = "THOUSAND"
arrGroup(2) = "MILLION"
arrGroup(3) = "BILLION"
strText = ""
If dblAmount > 0 Then
strCurrFormat = Format$(dblAmount, "#,###.00")
intLowDigit = InStr(strCurrFormat, ".") - 1
strCents = Mid$(strCurrFormat, intLowDigit + 2, 2)
intGroup = 0
While intLowDigit > 0
intDigit3 = CInt(Mid$(strCurrFormat, intLowDigit, 1))
If intLowDigit > 1 Then
intDigit2 = CInt(Mid$(strCurrFormat, intLowDigit - 1, 1))
Else
intDigit2 = 0
End If
If intLowDigit > 2 Then
intDigit1 = CInt(Mid$(strCurrFormat, intLowDigit - 2, 1))
Else
intDigit1 = 0
End If
strSubText = ""
If intDigit1 > 0 Then
strSubText = arrOnes(intDigit1) & " HUNDRED "
End If
If intDigit2 > 0 Then
If intDigit2 = 1 Then
strSubText = strSubText & arrOnes(intDigit3 + 10) & " "
Else
strSubText = strSubText & arrTens(intDigit2)
If intDigit3 > 0 Then
strSubText = strSubText & "-" & arrOnes(intDigit3)
End If
strSubText = strSubText & " "
End If
Else
If intDigit3 > 0 Then
strSubText = strSubText & arrOnes(intDigit3) & " "
End If
End If
If strSubText <> "" And arrGroupIdx <> 0 Then
strSubText = strSubText & arrGroup(arrGroupIdx) & " "
End If
strText = strSubText & strText
intLowDigit = intLowDigit - 4
arrGroupIdx = arrGroupIdx + 1
Wend
strText = strText + "& " + strCents + "/100"
If Left$(strText, 1) = "&" Then
strText = "NO " + strText
End If
If intLength > 0 Then
If Len(strText) > intLength Then
strText = strCurrFormat
Else
For intCounter = 1 To (intLength - Len(strText))
strText = strText + "*"
Next intCounter
End If
End If
End If
ConvertCurrencyToText = strText
End Function

[June7]

Pad just means to fill in with characters so every result is the same length.

Example: Padding 3, 457, 89 with leading zeros so each is 3 characters in length would display as 003, 457, 089.

This procedure will pad the results with asterisks at the end of string.

So when you call the function need to provide an integer for the intLength argument.

[Derrick T. Davidson]

In the expression if I put in a number e.g. =NumWords ( [AccessTotalsCount],999999 ) I get the following in results box #Num!

[June7]

Well, 999999 means you want a string of 999,999 characters. Maybe too much for Access to handle.

How many maximum characters do you want displayed? If you don't want any padding, use 0.

[Derrick T. Davidson]

With a zero the text box shows #Name?, do I need to change the code in the module?

[June7]

Change last line (just before the End Function) to:

NumWord = strText

[Derrick T. Davidson]

You are the best "thank you" it works

[Derrick T. Davidson]

Hi I am back, can you tell me which parts of the code to delete to get rid of the decimal place, it is always a whole number and not currency?

[June7]

So this is not for printing checks?

Comment the following line (type an apostrophe at the beginning):

'strText = strText + "& " + strCents + "/100"

[Derrick T. Davidson]

No not checks, its for a summary report that requires records shown in words and numbers. I put 'strText = strText + "& " + strCents + "/100" in the start comments, no change. I put 'strText = strText + "& " + strCents + "/100" at the end before End Function, no change. As it is a comment I am unsure how this would change the outcome?

[June7]

All you had to do was put an apostrophe in front of the line that was already in the code to comment out the line - 16th up from the end.

[Derrick T. Davidson]

Whoops sorry an old guy taking first faltering steps (gotta love downsizing) Thank you for your patience all works great now.