Microsoft Office Tutorials and References
In Depth Information
Using Custom Functions
‘** Add the word “Thousand” if necessary
If AmountPassed > 999.99 And LoopCount = 2 Then
English = English + “ Thousand “
‘** Do pass through next three digits
LoopCount = LoopCount + 1
StartVal = StartVal + 3
‘** Done: Return English with Pennies/100 tacked on
NumWord = Trim(English) & “ and “ & Pennies & “/100”
You can test any custom function you create right in the Immediate window.
To test the NumWord() function, for example, you’d use a question mark
followed by a space and then NumWord with some number you want to convert
to words. Suppose that you type the following and press Enter:
NumWord() does its thing and spits back the result:
One Hundred Twenty Three Thousand Four Hundred Fifty Six and 78/100
In real life, of course, you’d most likely use NumWord() in a database that
has the capability to print checks. Suppose that in the same database as the
NumWord() function, you have a table like the one in Figure 3-8 that contains
information for writing checks.
Next, you need to create a report format that can print on preprinted checks.
Most of the controls on that report come straight from the table, except that
you need one calculated control to print the check amount in words. That
calculated control uses the expression =NumWord(CheckAmount). In other
words, it uses the NumWord() function in the same way that you use a built-in
Access function. Figure 3-9 shows what that report design looks like.