Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: covert $1,000 into One Thousand

    I have a database that takes user inputed data from a form and merges it with word. Some of the data is currency. So if the user puts in $3,000 I need it to show up as three thousand in the word document. Is this possible??

  2. #2
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    It is certainly possible and will take a good bit of coding. I will do it for 100$ payable via paypal.

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    wow, I didn't know it was that involved. I would have to pass on the $100 bid but thanks for the offer.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There was someone who found a module to do this once upon a time ont his forum.. I would dig around the search feature and see what you can find. It's more tedious then anything. Lots of coding. You have to manually write out one, two, three, twenty etc etc in the code.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    It was a joke, but it is not as simple as selecting a custom formating setting. If you know some VBA it would be a fun little procedure to create.

  6. #6
    Join Date
    Mar 2004
    Posts
    361
    I know very little vba so that's why I tried to forum first. I'll do a search and see what I find. If you have an outline of some completed code I would love to take a look at it. Thanks.

  7. #7
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    this link might help you I guess.
    http://www.barasch.com/excel/cCo11432.htm

    it is an excel example but it is VBA and should work with Access also.
    ghozy.

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Just copy and the next code (taken from Leban's Web site)
    PHP Code:
     Converting  the currency value "$531.20" into the text "Five hundred thirty one and twenty cents."

    '************ Code Start **********
    '
    This code was originally written by Joe Foster.
    'It is not to be altered or distributed,
    '
    except as part of an application.
    'You are free to use it in any application,
    '
    provided the copyright notice is left unchanged.
    '
    Convert a currency value into an (AmericanEnglish string
    Function English (ByVal N As Currency) As String
        
    Const Thousand 1000@
        Const 
    Million Thousand Thousand
        
    Const Billion Thousand Million
        
    Const Trillion Thousand Billion

        
    If (0@) Then English "zero": Exit Function

        
    Dim Buf As String: If (0@) Then Buf "negative " Else Buf ""
        
    Dim Frac As CurrencyFrac Abs(Fix(N))
        If (
    0@ Or Frac <> 0@) Then N Abs(Fix(N))
        
    Dim AtLeastOne As IntegerAtLeastOne >= 1

        
    If (>= TrillionThen
            Debug
    .Print N
            Buf 
    Buf EnglishDigitGroup(Int(Trillion)) & " trillion"
            
    Int(Trillion) * Trillion ' Mod overflows
            If (N >= 1@) Then Buf = Buf & " "
        End If
        
        If (N >= Billion) Then
            Debug.Print N
            Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
            N = N - Int(N / Billion) * Billion ' 
    Mod still overflows
            
    If (>= 1@) Then Buf Buf " "
        
    End If

        If (
    >= MillionThen
            Debug
    .Print N
            Buf 
    Buf EnglishDigitGroup(Million) & " million"
            
    N Mod Million
            
    If (>= 1@) Then Buf Buf " "
        
    End If

        If (
    >= ThousandThen
            Debug
    .Print N
            Buf 
    Buf EnglishDigitGroup(Thousand) & " thousand"
            
    N Mod Thousand
            
    If (>= 1@) Then Buf Buf " "
        
    End If

        If (
    >= 1@) Then
            Debug
    .Print N
            Buf 
    Buf EnglishDigitGroup(N)
        
    End If

        If (
    Frac 0@) Then
            Buf 
    Buf " exactly"
        
    ElseIf (Int(Frac 100@) = Frac 100@) Then
            
    If AtLeastOne Then Buf Buf " and "
            
    Buf Buf Format$(Frac 100@, "00") & "/100"
        
    Else
            If 
    AtLeastOne Then Buf Buf " and "
            
    Buf Buf Format$(Frac 10000@, "0000") & "/10000"
        
    End If

        
    English Buf
    End 
    Function
    ' Support function to be used only by English()
    Private Function EnglishDigitGroup (ByVal N As Integer) As String
        Const Hundred = " hundred"
        Const One = "one" :Const Two = "two" : Const Three = "three"
        Const Four = "four":Const Five = "five":Const Six = "six"
        Const Seven = "seven":Const Eight = "eight"
        Const Nine = "nine"
        Dim Buf As String: Buf = ""
        Dim Flag As Integer: Flag = False

        '
    Do hundreds
        Select 
    Case (100)
        Case 
    0Buf "":  Flag False
        
    Case 1Buf One HundredFlag True
        
    Case 2Buf Two HundredFlag True
        
    Case 3Buf Three HundredFlag True
        
    Case 4Buf Four HundredFlag True
        
    Case 5Buf Five HundredFlag True
        
    Case 6Buf Six HundredFlag True
        
    Case 7Buf Seven HundredFlag True
        
    Case 8Buf Eight HundredFlag True
        
    Case 9Buf Nine HundredFlag True
        End Select
       
        
    If (Flag <> FalseThen N N Mod 100
        
    If (0Then
            
    If (Flag <> FalseThen Buf Buf " "
        
    Else
            
    EnglishDigitGroup Buf
            
    Exit Function
        
    End If      
        
    'Do tens (except teens)
        Select Case (N \ 10)
        Case 0, 1: Flag = False
        Case 2: Buf = Buf & "twenty": Flag = True
        Case 3: Buf = Buf & "thirty": Flag = True
        Case 4: Buf = Buf & "forty": Flag = True
        Case 5: Buf = Buf & "fifty": Flag = True
        Case 6: Buf = Buf & "sixty": Flag = True
        Case 7: Buf = Buf & "seventy": Flag = True
        Case 8: Buf = Buf & "eighty": Flag = True
        Case 9: Buf = Buf & "ninety": Flag = True
        End Select
       
        If (Flag <> False) Then N = N Mod 10
        If (N > 0) Then
            If (Flag <> False) Then Buf = Buf & "-"
        Else
            EnglishDigitGroup = Buf
            Exit Function
        End If
        
        '
    Do ones and teens
        Select 
    Case (N)
        Case 
    0' do nothing
        Case 1: Buf = Buf & One
        Case 2: Buf = Buf & Two
        Case 3: Buf = Buf & Three
        Case 4: Buf = Buf & Four
        Case 5: Buf = Buf & Five
        Case 6: Buf = Buf & Six
        Case 7: Buf = Buf & Seven
        Case 8: Buf = Buf & Eight
        Case 9: Buf = Buf & Nine
        Case 10: Buf = Buf & "ten"
        Case 11: Buf = Buf & "eleven"
        Case 12: Buf = Buf & "twelve"
        Case 13: Buf = Buf & "thirteen"
        Case 14: Buf = Buf & "fourteen"
        Case 15: Buf = Buf & "fifteen"
        Case 16: Buf = Buf & "sixteen"
        Case 17: Buf = Buf & "seventeen"
        Case 18: Buf = Buf & "eighteen"
        Case 19: Buf = Buf & "nineteen"
        End Select

        EnglishDigitGroup = Buf
    End Function
    '
    ************ Code End ********** 

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by tjarvas
    I have a database that takes user inputed data from a form and merges it with word. Some of the data is currency. So if the user puts in $3,000 I need it to show up as three thousand in the word document. Is this possible??
    Hi tjarvas,
    This came from a cd in a book that I once had for what you are needing. Not really sure of the author's full name but his first name was Alan.
    Code:
    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
    
        '** The array below, and other variables, are dimensioned
        '** in the Declarations section.
        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)
    
        '** If amount passed is 0 just return "Zero"...
        If AmountPassed = 0 Then
            NumWord = "Zero"
            Exit Function
        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
    That was the code that runs when this command button is clicked:

    Code:
    '------------------------------------------------------------
    ' Command7_Click
    '
    '------------------------------------------------------------
    Private Sub TestNumWord_Click()
    On Error GoTo TestNumWord_Click_Err
        DoCmd.GoToControl "CloseNumWordDemoForm"
    
    TestNumWord_Click_Exit:
        Exit Sub
    
    TestNumWord_Click_Err:
        MsgBox Error$
        Resume TestNumWord_Click_Exit
    
    End Sub
    Attached is a bitmap of the program. Simply put, the user types in a dollar amount in numbers and it converts it to text. As stated, it is very lengthy coding. Hope you can make it work.

    have a nice one,
    Bud
    Attached Thumbnails Attached Thumbnails NumbersToWords.bmp  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •