Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    72

    Unanswered: Dlookup ultimate

    HI
    I have a table tblgeography that contains the following fields
    country....................usa
    currency..................dollar
    currencyformat.........$
    currencytowords.......dollartowords

    the data is there for lots of other countries also

    the currencytowords field have dollartowords as its value for usa, eurotowords for Europe, poundstowords for Britain

    in the module, I have created the following function
    =dollartowords()
    =eurotowords()
    =gbptowords()
    I put a neumerical value in the brackets and it converts it to words appropriately wrt to the currency
    example
    =dollartowords(1.50) gives one dollar and 50 cents only

    on a report I have a field called paycurrency that displays the currency
    The issue
    I have to convert the number in a text box to words depending on the paycurrency value

    what I did
    =DLookUp("CURRENCYTOWORDS"," TBLGEOGRAPHY","GEOCURRENCY ='" & [PAYCURRENCY] & "'")
    THIS RETURNS A VALUE OF ..eurotowords

    from here iam not able to take it forward
    using the value returned, I want to convert the number to words

    any help is appreciated
    thanks in advance
    Hemanth

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    You need a recursive function like this:

    Code:
    'This function spells out the number given (like on a check)
    'DO NOT USE COMMAS
    
    Public Function SpellNum(Optional ByVal psNum)
    Dim vTxt, vNum, vWord, vN1, vN2
    Dim X As Integer, l As Integer, i As Integer
    Dim vCents
    
    If IsMissing(psNum) Then
       SpellNum = ""
       Exit Function
    End If
    
    If InStr(psNum, ".") > 0 Then
         i = InStr(psNum, ".")
         vCents = Mid(psNum, i + 1)
         psNum = Left(psNum, i - 1)
    End If
    
    
    If Len(psNum) = 1 And Val(psNum) = 0 Then
       SpellNum = "ZERO"
       Exit Function
    End If
    
    
    If Val(psNum) = 0 Then
      i = -1
    Else
      i = Len(psNum)
    End If
    
    Select Case i
    Case -1
       SpellNum = ""
       Exit Function
    
    Case 1
        X = 0
        Select Case Val(psNum)
        Case 0
            vTxt = ""
        Case 1
            vTxt = "ONE "
        Case 2
            vTxt = "TWO "
        Case 3
            vTxt = "THREE "
        Case 4
            vTxt = "FOUR "
        Case 5
            vTxt = "FIVE "
        Case 6
            vTxt = "SIX "
        Case 7
            vTxt = "SEVEN "
        Case 8
            vTxt = "EIGHT "
        Case 9
            vTxt = "NINE "
        End Select
        
    Case 2
        X = 0
        Select Case Val(psNum)
           Case 10
             vTxt = "TEN "
           Case 11
             vTxt = "ELEVEN "
           Case 12
             vTxt = "TWELVE "
           Case 13
             vTxt = "THIRTEEN "
           Case 14
             vTxt = "FORTEEN "
           Case 15
             vTxt = "FIFTEEN "
           Case 16
             vTxt = "SIXTEEN "
           Case 17
             vTxt = "SEVENTEEN "
           Case 18
             vTxt = "EIGHTEEN "
           Case 19
             vTxt = "NINETEEN "
           Case Else
              Select Case Val(Left(psNum, 1))
                Case 2
                    vTxt = "TWENTY "
                Case 3
                    vTxt = "THIRTY "
                Case 4
                    vTxt = "FORTY "
                Case 5
                    vTxt = "FIFTY "
                Case 6
                    vTxt = "SIXTY "
                Case 7
                    vTxt = "SEVENTY "
                Case 8
                    vTxt = "EIGHTY "
                Case 9
                    vTxt = "NINETY "
              End Select
              
              X = Val(Right(psNum, 1))
              vTxt = vTxt & SpellNum(X)
              X = 0
        End Select
    
    Case 3
        X = 2
        vTxt = psNum
        vWord = "HUNDRED "
    
    Case 4, 5, 6
        X = 3
        vWord = "THOUSAND "
    
    Case 7, 8, 9
        X = 6
        vWord = "MILLION "
    
    Case 10, 11, 12
        X = 9
        vWord = "BILLION "
    End Select
    
    If X > 0 Then
        l = Len(psNum) - X
        vN1 = Left(psNum, l)
        vN2 = Mid(psNum, l + 1)
        vTxt = SpellNum(vN1) & vWord
        vTxt = vTxt & SpellNum(vN2)
    End If
    
    If Len(vCents) > 0 Then
       SpellNum = vTxt & " and " & vCents & " cents"
    Else
       SpellNum = vTxt
    End If
    End Function

  3. #3
    Join Date
    May 2009
    Posts
    72
    thanks for the reply ranman
    but I have already created a function to convert to words

    my only requirement is to change the currency type based on a parameter

    quote from my previous post

    in the module, I have created the following function
    =dollartowords()
    =eurotowords()
    =gbptowords()
    I put a neumerical value in the brackets and it converts it to words appropriately wrt to the currency
    example
    =dollartowords(1.50) gives one dollar and 50 cents only

    on a report I have a field called paycurrency that displays the currency
    The issue
    I have to convert the number in a text box to words depending on the paycurrency value

    what I did
    =DLookUp("CURRENCYTOWORDS"," TBLGEOGRAPHY","GEOCURRENCY ='" & [PAYCURRENCY] & "'")
    THIS RETURNS A VALUE OF ..eurotowords

    from here iam not able to take it forward
    using the value returned, I want to convert the number to words

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you would be better off passing the task into a handler function, which in turn calls the appropriate currency formatter

    public sub HandleCurrencyToWords(Amount as double, CurrencyCode as string, CountryCode as String)
    select case CurrencyCode
    case is "USD"
    .....insert call to USdollartowords using whatever parameters you need
    case is "CAD","AUD","ECD" .......
    .....insert call to nonUSdollartowords using whatever parameters you need
    case is "GBP"
    .....insert call to GBPtowords using whatever parameters you need
    case is "EUR"
    select case CountryCode
    case is "IRL" ...select the Irish handler
    case is...... repeat for the other 29 odd countries in th4e EUro
    end select
    end select
    end sub

    bear in mind inside the EURO zone some countries use "." to separator thousands, "," decimals and some use the UK/US convention
    not all countries use thousands, some use hundreds

    eg 1234.56 could be
    one thousand, two hundred and thirty four <currency> and fifty six
    twelve hundred and thirty four <currency> and fifty six

    others such as India use a different base altogether
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2009
    Posts
    72
    thanks healdem for the reply
    can you please post one example code
    it would be great and appreciated

    thanks

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    public sub HandleCurrencyToWords(Amount as double, CurrencyCode as string, CountryCode as String)
    select case CurrencyCode 
    case is "USD"
    .....insert call to USdollartowords using whatever parameters you need
    case is "CAD","AUD","ECD" .......
    .....insert call to nonUSdollartowords using whatever parameters you need
    case is "GBP"
    .....insert call to GBPtowords using whatever parameters you need
    case is "EUR"
    select case CountryCode
    case is "IRL" ...select the Irish handler
    case is...... repeat for the other 29 odd countries in th4e EUro
    end select
    end select
    end sub
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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