Using the Num Word function

For the sake of example, assume that you already put NumWord() into a standard module in your database. You already have a table that contains data to be printed on checks. Just to give this whole example some context, suppose you have a table with field names and data types similar to those shown in the sample Payables table in Figure 11-6. The left side of the figure shows the table's structure, and the right side of the figure shows some sample data in the table.

Figure 11-6:

Sample field names and data types for printing checks.

Figure 11-6:

Sample field names and data types for printing checks.

Next, you need to create a report format that's capable of printing on the checks. When you get to the part of the report where the check amount needs to be printed, just add a calculated control that prints the NumWord of the numeric check amount. For example, in the PrintChecks report shown in Figure 11-7, you can see where I've placed various controls to line up with blanks on each check (even though I don't really have a preprinted check here to show you). Presumably, all the other info the check needs is already printed on the check.

Figure 11-7:

Sample report format for printing checks.

Figure 11-7:

Sample report format for printing checks.

In the report format shown in Figure 11-7, the PayTo and CheckAmt fields come straight from the underlying Payables table. The check date and check amount in words are both calculated controls. The calculated control for printing the check date has as its Control Source property the expression = Date(), which prints the current date on the check. The calculated control for printing the check amount in words contains the following expression as its Control Source property:


There, the field name CheckAmt refers to the field named CheckAmt, which contains the check amount expressed as a number. Once again, the example illustrates how after you add a custom function to a standard module, you can use that function in any place you'd use a built-in function. For example, the check date is printed by using the built-in Date() function, and the check amount (in words) is printed by the custom NumWord() function.

Figure 11-8 shows a Print Preview for the report in Figure 11-7 (with some dashed lines artificially thrown in to make it easier to see where each check begins and ends). As mentioned, here I'm assuming any other information that needs to be printed on the check is already on the checks.

Figure 11-8:

Print Preview of sample check-printing report.

a PrintQiecks Rpl : Raport

Acme Widgets

One Hunidrejd.Twenty Three and45/100


Fifty.Potif Thousand Three Hundred TwenfijiQne and 90/1®


-Wanda Bea Nerd $456,789.00

Four Hundred Fifty Six Thousand-Seven Hundred Eighty Nine and 00/100

How NumWord() works

NumWord() is a fairly lengthy procedure mainly because the rules for converting numbers to words, in English, are a little complicated. But like any procedure, NumWord() is just a series of small decisions and steps needed to get the job done.

The first line of the procedure, as follows, defines the name of the procedure, NumWord(), and declares that it will accept a number Currency value (number) as an argument. Whatever number gets passed to the argument is referred to as AmountPassed in the rest of the procedure. The As String part at the end declares that NumWord() will return a string (text) to whatever called the function.

Function NumWord(AmountPassed As Currency) As String

The next lines declare some variables used for temporary storage by the procedure. Because there are lots of things to keep track of in this procedure, you need quite a few variables to store bits of information. In the following Dim statements, I'm just declaring the names and data types of the variables. You can see how to put them to use later in the procedure:

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

Next, the statement Dim EngNum(90) As String declares any array of variables, all containing text. The variables created by the statement will be named EngNum(0), EngNum(1), EngNum(2), and so forth, on up to EngNum(90). The Dim statement, as always, just sets aside space for those 90 variables. The variables don't actually contain any data at first:

Dim EngNum(90) As String

The next statements assign text to some of the variables that the Dim statement just declared. You don't need all 90 variables here — just enough of them to cover every possible unique number word. For example, you need ninety as a unique word, but you don't need ninety-one as a unique word because that can be built from two words: ninety and one.

The subscript for each variable matches the word that the variable contains. For example, EngNum(1) contains "One", EngNum(11) contains "Eleven", EngNum(70) contains "Seventy", and so forth. In a sense, you've already solved part of the problem just by having the array subscript match the word that you need:

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"


= "Ten"


= "Eleven"


= "Twelve"


= "Thirteen"


= "Fourteen"


= "Fifteen"


= "Sixteen"


= "Seventeen"


= "Eighteen"


= "Nineteen"


= "Twenty"


= "Thirty"


= "Forty"


= "Fifty"


= "Sixty"


= "Seventy"


= "Eighty"


= "Ninety"

For the lowdown on arrays, see Chapter 4.

For the lowdown on arrays, see Chapter 4.

With all the needed variables declared, the procedure can get to work on translating whatever number was passed to it. The first If...End If block takes care of the problem of a zero or null value being passed to the function. The built-in Nz() (Null-To-Zero) converts a null value to a zero. Thus, the If statement Nz(AmountPassed) = 0 Then really says, If the amount passed to me to work on is zero (or a null), then do the lines below me up to End If. Otherwise, ignore those statements.

So what happens if AmountPassed is a zero or null? The statement NumWord = "VOID" makes the return value for the function into the word VOID, and the Exit Function statement tells VBA to just bail out of the procedure now without doing anything else:

'** If zero or null

passed, just return "VOID".

If Nz(AmountPassed)

= 0 Then

NumWord = "VOID"

Exit Function

End If

Assuming that the amount passed to NumWord() is not a zero or null, execution then picks up at the following statement. This one is a little tricky. It uses the built-in Format function to make a string named strNum that exactly matches the amount passed. However, this string has exactly nine zeroes to the left of the decimal point and also two to the right. Suppose NumWord gets called with NumWord(7609511.98). By the time the following statement executes, the AmountPassed variable (a number) contains 7609511.98, and strNum contains (as a string) 007609511.98. Having those leading zeroes in place makes it easier to make decisions about how to handle the number later in the procedure:

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

So getting back with the NumWord(7609511.98) call, after the preceding statement executes, you have two copies of the amount passed to work with: the original AmountPassed (a number) and strNum, which is basically that same number with a fixed amount of leading zeroes, as follows:

AmountPassed = 7609511.98 strNum = "007609511.98"

Next, the following statement grabs the last two digits off of StrNum and stores that value in a variable named Pennies.

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

In this example where I'm using 7609511.98 as the number passed, the variable named Pennies contains the following after the preceding statement executes:

Pennies = "98"

Now you need to get some starting values in some variables for the code to follow. The variable named English (which will eventually contain the entire number word) starts off as a null (" "). LoopCount and StartVal each get values of 1 . You can see how to use those variables in the code that follows:

'Prep other variables for storage.

Next, start a loop that will repeat until the LoopCount variable is greater than three. Within that tool, the first thing you do is peel off chunks of strNum variable and assign them to integer variables:

'** 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

Getting back to the initial strNum number, 007609511.98, by the time the preceding statements execute, the following variables contain the following values:

Chunk = "007" Hundreds = 0 Tens = 7 Ones = 7

The next statement says, If the value of chunk (007 right now) is greater than 99, add EngNum(Hundreds) plus the word hundred to the string. In the current example, where Chunk is not greater than 99, nothing happens in this

'** Do the hundreds portion of 3-digit number If Val(Chunk) > 99 Then

English = English & EngNum(Hundreds) & " Hundred " End If

The next statements set the Boolean variable TensDone to False. Then the next statement says, If the Tens portion is less than 10, add a blank space and

EngNum(Ones) to the English variable and change TensDone to True.

'** 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

In this case, where Tens contains 7, the statement is true. By the time the preceding statements have executed (given the sample number), the following variables have the following values:

English = " Seven" TensDone = True

The next If...End If statement deals with numbers in the range of 11-19. It says, If the Tens number is between 11 and 19, add EngNum(Tens) to English and set TensDone to True. In this example, Tens is 7, which is not between 11 and 19, so this If block is skipped over. The contents and English and TensDone haven't changed:

'** Is it a


If (Tens >=

11 And Tens <=

19) Then

English =

= English & Engf



= True

End If

The next block deals with Tens values that are evenly divisible by 10, such as 10, 20, 30, 40, 50, and so forth up to 90. In this case, where Tens contains 7 (which is not evenly divisible by 10), nothing happens, so the English and TensDone variables hang on to their current values:

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

The next If block kicks in only if the Tens portion of the number is still unresolved: that is, only if TensDone is still False. In this case, where TensDone got set to True already, the whole If...End If block is once again skipped over:

'** Or is it none of the above? If Not TensDone Then

English = English & EngNum((Int(Tens / 10)) * 10) English = English & " " & EngNum(Ones) End If

Next look at adding the word million to the word. The If statement says, If the amount passed is greater than 999,999.99 and the LoopCount variable equals one, add the word Million to English.

'** Add the word "Million" if necessary If AmountPassed > 999999.99 And LoopCount = 1 Then

English = English + " Million " End If

Using the running example, the number passed is greater than 999,999.99, and right now LoopCount does equal 1. By the time the preceding If statement executes, the English variable has had the word Million tacked onto it, as follows:

English = "Seven Million"

The next statement says that if the amount passed is greater than 999.99 and LoopCount equals 2, tack on the word Thousand. In the running example, where LoopCount currently equals 1, this whole block of code is skipped over:

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

English = English + " Thousand " End If

The next statements increase the value of the LoopCount variable by 1 and increase the value of the StartVal variable by 3; then the Loop statement sends execution back up to the Do While LoopCount <= 3 statement for the next pass through the loop.

Converting the rest of the number is more of the same. The next pass through the loop just has to work with the next three-digit chunk of strNum. In this example, where strNum contains 007609511.98, the next three digits after 007 are 609. By the time Chunk, Hundreds, Tens, and Ones have received their new values near the top of the loop, those variables contain the following values:

Looking through just the If...End If statements that prove true for this second pass through the loop, the statement Val(Chunk) > 99 is true this time. Thus, the statement English = English & EngNum(Hundreds) & " Hundred " executes adding EngNum(6) plus the word "Hundred" to EngNum. By the time that statement has executed, the English variable has a new value:

English = "Seven Million Six Hundred"

The statement If Tens < 10 Then is also True on this second pass through the loop, so the statement English = English & " " & EngNum(Ones) adds a space and EngNum(9) to the English variable:

English = "Seven Million Six Hundred Nine"

No other If statements will prove True here until If AmountPassed > 999.99 And LoopCount = 2 Then executes. Because it's true that AmountPassed is greater than 999.99 and LoopCo unt — 2 right now, the statement English — English + " Thousand " executes, and the English variable contains

English — "Seven Million Six Hundred Nine Thousand"

Now you're at the bottom of the loop again, where LoopCount gets increased by 1, and StartVal gets increased by 3. By the time the Loop statement sends control back up to the Do While statement, those variables contain the following values:

LoopCount = 3 StartVal = 9

At the top of the loop, the Chunk, Hundreds, Tens, and Ones variable all get new values, as follows, by peeling off the last three digits to the left of the decimal point:

Chunk = "511" Hundreds = 5 Tens = 11 Ones = 1

Once again, execution goes through all the statements, but only certain If...End If statements will prove true. For example, the first True statement, If Val(Chunk) > 99, executes the statement English = English & EngNum(5) & " Hundred ". By the time that If...End If block has executed, the English variable contains

English = "Seven Million Six Hundred Nine Thousand Five Hundred"

Going through the procedures that follow, the next If statement to prove

True is If (Tens >= 11 And Tens <= 19) Then. So the statement English = English & EngNum(11) executes, making the English variable contain

English = "Seven Million Six Hundred Nine Thousand Five Hundred Eleven"

No other If...End If statements execute. At the bottom of the loop where

LoopCount = LoopCount + 1, the value of LoopCount value increases to 4. The Do While loop repeats only while LoopCount is less than 4, so execution falls through the Loop statement, executing the statement NumWord = Trim(English) & " and " & Pennies & "/100".At that moment, NumWord (which is also the name of the function) gets " and ", the Pennies variable's value, and "/100" tacked on. The procedure then ends with an End Function statement. The value returned after calling NumWord(7609511.98) is

Seven Million Six Hundred Nine Thousand Five Hundred Eleven and 98/100

which, happily, is exactly right.

The procedure is designed to translate any number in the range of 0-999,999,999.99 where NumWord(999,999,999.99) returns

Nine Hundred Ninety Nine Million Nine Hundred Ninety Nine Thousand Nine Hundred Ninety Nine and 99/100

If that's not big enough for you (because you print checks for a billion dollars or more), you could probably talk me into personally modifying the procedure to accommodate your needs.

You can download the NumWord() custom function from www.coolnerds. com/vba.

Getting away from the nitty-gritty details of how a complex procedure like NumWord() works, the most important concepts to remember are that you can create your own custom function in Access. To make the function freely available to all the other objects in your database, you just have to put the custom function in a standard module. After you do that, you can treat your custom function as though it were any built-in function.

I admit that I got into some fairly intense code here in this last example. If you're thinking that I just made up that procedure in my head and jotted it down like notes, you're way off-base. Programming rarely works that way. It's all a matter of breaking down a large problem into small pieces. Then you attack one piece of the problem at a time, getting each little piece to work before moving on to the next piece.

Along the way, you'll generally run into a whole lot of error messages because it's tough to write code that just works right off the bat. You really need to create, test, and debug every little piece of code as you go along. That brings me to debugging strategies for building your code so that it always works and never crashes. Debugging is what the next chapter is all about.

0 0

Post a comment