Thank you so much for your help.
Sorry if I sent this twice but I couldn't find the reply I just sent.I found the code, per your instructions, and the hangup seems to be:
"Can handle numbers from 0 to $999,999.99"
'** Add the word "thousand" if necessary.
If AmountPassed > 999.99 And LoopCount = 1 Then
English = English + " Thousand "
and is why it won't work on numbers >=1M
Option Compare Database 'Use database order for string comparisons
Option Explicit 'Require explicit variable declaration
'This routine (c) 1994 Alan Simpson author of Understanding Access 2.0 by Sybex
'Variables used in NumWord() procedure defined here in Declarations.
Dim EngNum(90) As String
Dim StringNum As String, Chunk As String, English As String
Dim Hundreds As Integer, Tens As Integer, Ones As Integer
Dim LoopCount As Integer, StartVal As Integer
Dim TensDone As Integer
Dim Pennies As String
Static Function NumWord(ByVal AmountPassed As Currency) As String
'** Convert a number to words for filling in the Amount of a check
'** Example: NumWord(120.45) returns ONE HUNDRED TWENTY AND 45/100
'** Can handle numbers from 0 to $999,999.99
'** Created by Alan Simpson: Fax (619)756-0159
'** First working version, not yet fully tuned for speed or brevity.
'** The array below, and other variables, are dimensioned
'** in the Declarations section.
'** Fill EngNum array, if it's not filled already)
If Not EngNum(1) = "One" Then
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"
End If
'** Convert incoming Currency value to a string for parsing.
StringNum = Format$(AmountPassed, "000000.00")
'** Initialize other variables
English = ""
LoopCount = 1
StartVal = 1
Pennies = Mid$(StringNum, 8, 2)
'** Just in case the check is for less than a buck...
If AmountPassed < 1 Then
English = "Zero"
End If
'** Now do each 3-digit section of number.
While LoopCount <= 2
Chunk = Mid$(StringNum, StartVal, 3)
Hundreds = Val(Mid$(Chunk, 1, 1))
Tens = Val(Mid$(Chunk, 2, 2))
Ones = Val(Mid$(Chunk, 3, 1))
'** 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 "thousand" if necessary.
If AmountPassed > 999.99 And LoopCount = 1 Then
English = English + " Thousand "
End If
'** Do pass through second three digits
LoopCount = LoopCount + 1
StartVal = 4
Wend
'** Done: Return english with pennies tacked on.
NumWord = Trim(English) & " and " & Pennies & "/100"
End Function
---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :
hi Mike,
adding onto Bill's comment ...
> "walk you through it"
yes. NumWord is not a built-in Access function; it is something custom written in your project, so we need to see what it says to help you change it ... and you need to know how to navigate the logic anyway, if you are to maintain this application.
Bill said, "Alt+F11 ... Then press Ctrl+F to open the Find dialog box. Type in NumWord and make sure you choose to search the entire project. That will find the function wherever it is."
keep searching the VBA code for the function declaration (and make sure the scope to find is PROJECT not just procedure or module) ...it will be something like:
[Public] Function NumWord( ... ) [as datatype]
copy that line, down to and including the one that says "End Function"
~~~
>"When I click on my contract form I get a screen that says:
General
Option Compare Database"that means you are looking at the top of a module sheet, which can contain declarations and code for many procedures. Go to page 24 of this file and read the section on Modules ... then go read the whole chapter ~ and maybe all of Access Basics?
http://www.accessmvp.com/Strive4Peace/Access_Basics_Crystal_080113_Chapter_02.pdf
If you've inherited a project someone else developed, take comfort in the fact that Option Explicit is at the top of the module ... so someone knew a bit about what they were doing ... so that is good.
~~~
Code just says:
Option Compare Database
Private Sub Label177_BeforeUpdate(Cancel As Integer)
End Sub
~~~
a control BeforeUpdate event might be the wrong place, to use this function, even though it may work. (Since it information that can be converted anytime, a control could be calculated with an expression to call the function to convert a number to words on the Control Source. If this text will be used to write a check, and the text is being stored in a field, then a control AFTERupdate event is a better place to calculate that since BeforeUpdate is typically reserved for data validation. If you store calculated fields, it is good to have a way for administrators to update calculations.)
~~~
as Bill mentioned, please remember to keep old messages to the thread when your respond, thanks
~~~
a control BeforeUpdate event might be the wrong place, to use this function, even though it may work. (Since it information that can be converted anytime, a control could be calculated with an expression to call the function to convert a number to words on the Control Source. If this text will be used to write a check, and the text is being stored in a field, then a control AFTERupdate event is a better place to calculate that since BeforeUpdate is typically reserved for data validation. If you store calculated fields, it is good to have a way for administrators to update calculations.)
~~~
as Bill mentioned, please remember to keep old messages to the thread when your respond, thanks
~crystal
On 11/4/2018 4:48 PM, Bill Mosca wrmosca@... [MS_Access_Professionals] wrote:
No problem, Mike. We will walk you through it. First, open the database and press Alt+F11. That will take you to the code editor.
Then press Ctrl+F to open the Find dialog box. Type in NumWord and make sure you choose to search the entire project. That will find the function wherever it is.
Then copy and paste the entire funciotn into your post. Please try to remember to include the entire thread as some of us reply through email and need to see the whole conversation.
Sunday, 04 November 2018, 00:44PM -08:00 from mtamiazzo@... [MS_Access_Professionals] MS_Access_Professionals@yahoogroups.com:--
Regards,
Bill Mosca
Code just says:
Option Compare DatabasePrivate Sub Label177_BeforeUpdate(Cancel As Integer)End Sub
__._,_.___
Posted by: mtamiazzo@gmail.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (10) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
SPONSORED LINKS
.
__,_._,___
Tidak ada komentar:
Posting Komentar