Senin, 05 November 2018

Re: [MS_AccessPros] Re: Access 2010 =NumWord Expression

 

Hi Mike - I'm in the middle of a system upgrade and short on time. I bet Crystal or any of the other gurus here can make the modification you need.


-Bill


---In MS_Access_Professionals@yahoogroups.com, <mtamiazzo@gmail.com> wrote :

Hello Bill,

I really appreciate your help!

I see by the below code below:

"Can handle numbers from 0 to $999,999.99" 

and

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

is why I'm not able to apply it to 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

__._,_.___

Posted by: wrmosca@comcast.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (12)

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