Converting numbers to spelled words for checks (still)

[GBuser]

I found a very useful downloadable module from a similar question in July but I still don't know how to implement this in a report. I simply want to be able to write a check where the amount of the check is spelled out using words.

The module appears to be named "English." I have been trying to build an expression in a report that returns the spelled out amount of the check. I believe I'm close but my syntax isn't quite correct. My last attempt in the source line was:

=English(Check) where English is the module
and check is the amount

Thanks for any help.

 

[cghoga]

I found this code on the Website

http://www.mvps.org/access/

It has been very useful to me.

************* Code Start **********
'This code was originally written by Joe Foster.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Joe Foster
'
' Convert a currency value into an (American) English string
Function English (ByVal N As Currency) As String
Const Thousand = 1000@
Const Million = Thousand * Thousand
Const Billion = Thousand * Million
Const Trillion = Thousand * Billion

If (N = 0@) Then English = "zero": Exit Function

Dim Buf As String: If (N < 0@) Then Buf = &quot;negative &quot; Else Buf = &quot;&quot;
Dim Frac As Currency: Frac = Abs(N - Fix(N))
If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
Dim AtLeastOne As Integer: AtLeastOne = N >= 1

If (N >= Trillion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & &quot; trillion&quot;
N = N - Int(N / Trillion) * Trillion ' Mod overflows
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= Billion) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & &quot; billion&quot;
N = N - Int(N / Billion) * Billion ' Mod still overflows
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= Million) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Million) & &quot; million&quot;
N = N Mod Million
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= Thousand) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N \ Thousand) & &quot; thousand&quot;
N = N Mod Thousand
If (N >= 1@) Then Buf = Buf & &quot; &quot;
End If

If (N >= 1@) Then
Debug.Print N
Buf = Buf & EnglishDigitGroup(N)
End If

If (Frac = 0@) Then
Buf = Buf & &quot; exactly&quot;
ElseIf (Int(Frac * 100@) = Frac * 100@) Then
If AtLeastOne Then Buf = Buf & &quot; and &quot;
Buf = Buf & Format$(Frac * 100@, &quot;00&quot & &quot;/100&quot;
Else
If AtLeastOne Then Buf = Buf & &quot; and &quot;
Buf = Buf & Format$(Frac * 10000@, &quot;0000&quot & &quot;/10000&quot;
End If

English = Buf
End Function

' Support function to be used only by English()
Private Function EnglishDigitGroup (ByVal N As Integer) As String
Const Hundred = &quot; hundred&quot;
Const One = &quot;one&quot;
Const Two = &quot;two&quot;
Const Three = &quot;three&quot;
Const Four = &quot;four&quot;
Const Five = &quot;five&quot;
Const Six = &quot;six&quot;
Const Seven = &quot;seven&quot;
Const Eight = &quot;eight&quot;
Const Nine = &quot;nine&quot;
Dim Buf As String: Buf = &quot;&quot;
Dim Flag As Integer: Flag = False

'Do hundreds
Select Case (N \ 100)
Case 0: Buf = &quot;&quot;: Flag = False
Case 1: Buf = One & Hundred: Flag = True
Case 2: Buf = Two & Hundred: Flag = True
Case 3: Buf = Three & Hundred: Flag = True
Case 4: Buf = Four & Hundred: Flag = True
Case 5: Buf = Five & Hundred: Flag = True
Case 6: Buf = Six & Hundred: Flag = True
Case 7: Buf = Seven & Hundred: Flag = True
Case 8: Buf = Eight & Hundred: Flag = True
Case 9: Buf = Nine & Hundred: Flag = True
End Select

If (Flag <> False) Then N = N Mod 100
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & &quot; &quot;
Else
EnglishDigitGroup = Buf
Exit Function
End If

'Do tens (except teens)
Select Case (N \ 10)
Case 0, 1: Flag = False
Case 2: Buf = Buf & &quot;twenty&quot;: Flag = True
Case 3: Buf = Buf & &quot;thirty&quot;: Flag = True
Case 4: Buf = Buf & &quot;forty&quot;: Flag = True
Case 5: Buf = Buf & &quot;fifty&quot;: Flag = True
Case 6: Buf = Buf & &quot;sixty&quot;: Flag = True
Case 7: Buf = Buf & &quot;seventy&quot;: Flag = True
Case 8: Buf = Buf & &quot;eighty&quot;: Flag = True
Case 9: Buf = Buf & &quot;ninety&quot;: Flag = True
End Select

If (Flag <> False) Then N = N Mod 10
If (N > 0) Then
If (Flag <> False) Then Buf = Buf & &quot;-&quot;
Else
EnglishDigitGroup = Buf
Exit Function
End If

'Do ones and teens
Select Case (N)
Case 0: ' do nothing
Case 1: Buf = Buf & One
Case 2: Buf = Buf & Two
Case 3: Buf = Buf & Three
Case 4: Buf = Buf & Four
Case 5: Buf = Buf & Five
Case 6: Buf = Buf & Six
Case 7: Buf = Buf & Seven
Case 8: Buf = Buf & Eight
Case 9: Buf = Buf & Nine
Case 10: Buf = Buf & &quot;ten&quot;
Case 11: Buf = Buf & &quot;eleven&quot;
Case 12: Buf = Buf & &quot;twelve&quot;
Case 13: Buf = Buf & &quot;thirteen&quot;
Case 14: Buf = Buf & &quot;fourteen&quot;
Case 15: Buf = Buf & &quot;fifteen&quot;
Case 16: Buf = Buf & &quot;sixteen&quot;
Case 17: Buf = Buf & &quot;seventeen&quot;
Case 18: Buf = Buf & &quot;eighteen&quot;
Case 19: Buf = Buf & &quot;nineteen&quot;
End Select

EnglishDigitGroup = Buf
End Function
'************ Code End **********

 

[GBuser]

Thanks for the help. That is module I downloaded. I still don't know how to use it, however.

Is it =English(checks) or English(N), N=checks or something else? Neither seems to work for me. My old database program solved this with a simple formating (x,) and that was an old DOS based database. The complexity of this is frustrating to me.

 

[Tonto44]

How are you trying to use the function? Where in your report did you put the =English(checks) code?
What is &quot;checks&quot;? It should be a data field from a table or query. If not, it won't work in a report unless there's a way for the English() function to get the currency value of &quot;checks&quot; from your code.

If &quot;checks&quot; is a data field from the table/query that the report is based upon, then the syntax =English([checks]) should appear in the ControlSource property of the textbox on the report. Remember, the value of &quot;checks&quot; must be available to the report through its data source.
Cheers!!
Dave

 

[GBuser]

Thanks. Checks is a fieldname in a data table. I put the routine as a module. The code =English([checks]) was entered into the ControlSource line of a textbox in the report. I either get errors or &quot;Enter Parameter Value.&quot;

I also had a friend enter the routine as a format event and added a subroutine:

Private Sub Detail_Format (Cancel As Integer, FormatCount As Integer) Label60.Caption = English(Me.Report.Checks_written)
End Sub

where Checks_written is the name of the report. I still get asked to &quot;Enter Parameter Value.&quot;

 

[MichaelRed]

try &quot;([checks])&quot;. field names are usually delimited by the square bracketsin a y




MichaelRed
m.red@att.net

Searching for employment in all the wrong places