A Function to Print Check Amounts

Suppose you want to use Access to print checks from a table of payable amounts. You have your printer and your preprinted checks, and maybe you already created a report format to print the checks. What about the part of the check where you're supposed to write out the dollar amount, such as "One Hundred Thirty-Five and 49/100"? How can you get that part of the check printed? No built-in function is capable of doing that for you. And heaven knows you don't want to type all those words!

The solution is a custom function, like NumWord(), that takes as its argument any number and returns that number translated to words. For example, typing ? NumWord(1234.56) returns One Thousand Two Hundred Thirty-Four and 56/100. Because the NumWord() function is fairly lengthy, download it from www.dummies.com/go/access2007vbaprog rather than try to type it in yourself. Just in case, Listing-2 holds the whole kit and caboodle, which you can place in any standard module in any database.

'NumWord() converts a number to its words.
'For example, NumWord(999.99) returns
'Nine Hundred Ninety-Nine and 99/100.
Public Function NumWord(AmountPassed As Currency) As
	String

    'Declare some general working variables.
    Dim English As String, strNum As String
    Dim Chunk As String, Pennies As String
    Dim Hundreds As Integer, Tens As Integer
    Dim Ones As Integer, StartVal As Integer
    Dim LoopCount As Integer, TensDone As Boolean

    'Make array of number words called EngNum.
    Dim EngNum(90) As String
    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"

    '** If Zero or Null passed, just return "VOID".
    If Nz(AmountPassed) = 0 Then
	NumWord = "VOID"
	Exit Function
    End If

    '** strNum is the passed number converted to a string.
    strNum = Format(AmountPassed, "000000000.00")

    'Pennies variable contains last two digits of strNum
    Pennies = Mid(strNum, 11, 2)

    'Prep other variables for storage.
    English = ""
    LoopCount = 1
    StartVal = 1

    '** Now do each 3-digit section of number.
    Do While LoopCount <= 3
	Chunk = Mid(strNum, StartVal, 3) '3-digit chunk.
	Hundreds = Val(Mid(Chunk, 1, 1)) 'Hundreds portion.
	Tens = Val(Mid(Chunk, 2, 2)) 'Tens portion.
	Ones = Val(Mid(Chunk, 3, 1)) 'Ones portion.
	'** 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 "Million" if necessary.
	If AmountPassed > 999999.99 And LoopCount = 1 Then
	    English = English & " Million "
	End If

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

	'** Do pass through next three digits.
	    LoopCount = LoopCount + 1
	    StartVal = StartVal + 3
    Loop

    '** Done: Return English with Pennies/100 tacked on.
    NumWord = Trim(English) & " and " & Pennies & "/100"
End Function

That function is too long to show in the Code window (and too boring to discuss in any detail right now). Just assume that you already stuck the entire NumWord() procedure into a standard module in your database and now you want to use the procedure to print checks.