Solved

Currency Convert Formula

Posted on 1998-11-30
8
742 Views
Last Modified: 2010-08-05
I have total amount in kuwaiti dinar 12.344, this amount in numeric word and text.

In my invoice 12.344 total amount.

In invoice it must be in text like this (kuwaiti dinar twele and three hundred forty four only)

pls help to get this formula

Thanks
Shekar
0
Comment
Question by:bakery
8 Comments
 
LVL 2

Expert Comment

by:richtsteig
Comment Utility
Look at article ID

Q95640

of the Microsoft Knowledge Base (support.microsoft.com).
You will find the sourcecode of a function to convert a number into currency (english words)
0
 

Author Comment

by:bakery
Comment Utility
Unsatisfied with your answer, please send me exact formula for my change of currency in text .
I checked in support/microsoft.com but failed to get this answer.

pls this is very immportent to me to get from u only. Pls explain me the formula.

appreciate your reply

Thanks
0
 
LVL 2

Expert Comment

by:dm_14
Comment Utility
from
United Arab Emirates

Here is the code for the conversion :


Example : NumToWord (23.343)
KUWAITI DINAR Twenty Three and FILS 343 only


'===========================================================
'     =====
'     '*** This is the main function call
'===========================================================
'     =====

Function NumToWord(numval)

        Dim NTW, NText, dollars, cents, NWord, totalcents As String
        Dim decplace, TotalSets, cnt, LDollHold As Integer
        ReDim NumParts(9) As String 'Array for Amount (sets of three)
        ReDim Place(9) As String 'Array containing place holders
        Dim LDoll As Integer 'Length of the Dollars Text Amount
        Place(2) = " Thousand " '
        Place(3) = " Million " 'Place holder names for money
        Place(4) = " Billion " 'amounts
        Place(5) = " Trillion " '
        NTW = "" 'Temp value for the function
        NText = round_curr(numval) 'Roundup the cents to eliminate       cents gr 2
        NText = Trim(Str(NText)) 'String representation of amount
        decplace = InStr(Trim(NText), ".") 'Position of decimal 0 if none
        dollars = Trim(Left(NText, IIf(decplace = 0, Len(numval), decplace - 1)))
        LDoll = Len(dollars)
        cents = Trim(Right(NText, IIf(decplace = 0, 0, Abs(decplace - Len(NText)))))
        If Len(cents) = 1 Then
        cents = cents & "0"
        End If
        If (LDoll Mod 3) = 0 Then
        TotalSets = (LDoll \ 3)
        Else
        TotalSets = (LDoll \ 3) + 1
        End If
        cnt = 1
        LDollHold = LDoll
        Do While LDoll > 0
        NumParts(cnt) = IIf(LDoll > 3, Right(dollars, 3), Trim(dollars))
        dollars = IIf(LDoll > 3, Left(dollars, (IIf(LDoll < 3, 3, LDoll)) - 3), "")
        LDoll = Len(dollars)
        cnt = cnt + 1
        Loop
        For cnt = TotalSets To 1 Step -1 'step through NumParts array
        NWord = GetWord(NumParts(cnt)) 'convert 1 element of       NumParts
        iP1 = InStr(cnt, ".")
        NTW = NTW & NWord 'concatenate it to temp       variable
        If NWord <> "" Then NTW = NTW & Place(cnt)
        Next cnt 'loop through
        If LDollHold > 0 Then
        NTW = "KUWAITI DINAR " & NTW & " and " 'concatenate text
        Else
        NTW = " NO KUWAITI DINAR " & NTW & " and " 'concatenate text
        End If
        If Len(cents) > 3 Then
            cents = Left(cents, 3)
        End If
        totalcents = cents 'GetTens(cents) 'Convert cents part to word
        If totalcents = "" Then totalfils = "NO" 'Concat NO if cents=0
        NTW = NTW & "FILS " & totalcents & " only" 'Concat Dollars and Cents
        NumToWord = NTW 'Assign word value to


End Function

 '==========================================================
'     ======
 ' The following function converts a number from 1 to 9 to t
'     ext
 '==========================================================
'     ======

Function GetDigit(Digit)

        Select Case Val(Digit)
        Case 1: GetDigit = "One" '
        Case 2: GetDigit = "Two" '
        Case 3: GetDigit = "Three" '
        Case 4: GetDigit = "Four" ' Assign a numeric word value
        Case 5: GetDigit = "Five" ' based on a single digit.
        Case 6: GetDigit = "Six" '
        Case 7: GetDigit = "Seven" '
        Case 8: GetDigit = "Eight" '
        Case 9: GetDigit = "Nine" '
        Case Else: GetDigit = "" '
        End Select
End Function 'End function GetDigit - return to calling program

 '==========================================================
'     ======
 ' The following function converts a number from 10 to 99 to
'      text
 '==========================================================
'     ======

Function GetTens(tenstext)

        Dim GT As String
        GT = "" 'null out the temporary function value
        If Val(Left(tenstext, 1)) = 1 Then ' If value between 10-19
        Select Case Val(tenstext)
       Case 10: GT = "Ten" '
       Case 11: GT = "Eleven" '
       Case 12: GT = "Twelve" '
       Case 13: GT = "Thirteen" ' Retrieve numeric word
       Case 14: GT = "Fourteen" ' value if between ten and
       Case 15: GT = "Fifteen" ' nineteen inclusive.
       Case 16: GT = "Sixteen" '
       Case 17: GT = "Seventeen" '
       Case 18: GT = "Eighteen" '
       Case 19: GT = "Nineteen" '
       Case Else
        End Select
         
        Else ' If value between 20-99
        Select Case Val(Left(tenstext, 1))
         
       Case 2: GT = "Twenty " '
       Case 3: GT = "Thirty " '
       Case 4: GT = "Forty " '
       Case 5: GT = "Fifty " ' Retrieve value if it is
       Case 6: GT = "Sixty " ' divisible by ten
       Case 7: GT = "Seventy " ' excluding the value ten.
       Case 8: GT = "Eighty " '
       Case 9: GT = "Ninety " '
       Case Else
        End Select
        GT = GT & GetDigit(Right(tenstext, 1)) 'Retrieve ones place
        End If
         
        GetTens = GT ' Assign function return value.
End Function


'===========================================================
'     ======
' The following function converts a number from 0 to 999 to
'     text
'===========================================================
'     ======

Function GetWord(NumText)

        Dim GW As String, x As Integer
        GW = "" 'null out temporary function value
        If Val(NumText) > 0 Then
        For x = 1 To Len(NumText) 'loop the length of NumText times
       Select Case Len(NumText)
       Case 3:
        If Val(NumText) > 99 Then
        GW = GetDigit(Left(NumText, 1)) & " Hundred "
        End If
        NumText = Right(NumText, 2)
       Case 2:
        GW = GW & GetTens(NumText)
        NumText = ""
       Case 1:
        GW = GetDigit(NumText)
       Case Else
End Select

 Next x
 End If
 GetWord = GW 'assign function return value
End Function 'End function GetWord - Return to calling program


Function round_curr(currValue)

       '     '
       '     'This rounds any currency field
       '     '
       
       round_curr = currValue
End Function

0
 

Author Comment

by:bakery
Comment Utility
dm_14

Excellent and successfull amount in hundreds only
if the amount more than like 2,345.790 (two thousand three hundred forty five and fils 789)
this amount will not read, it is read only three hundred forty five and fils 789.

Why not reading thousand?
Why the amount reading less 1 fils
Why the amount if fils in last 500 it is reading only 5  but the 00 will not showing in print

pls modify and answer me
Thanks once again for yr excellent answer
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Accepted Solution

by:
Dedushka earned 50 total points
Comment Utility
Hi, bakery!

The translation of moneys in words from my point of view requires no more than 50 lines of a code (VBA MSA97).

----------
Function say(curAMOUNT As Currency) As String
Dim intTmp As Integer, strAmount As String, strKop As String

strAmount = Format$(curAMOUNT, "#0.000")
strKop = Right(strAmount, 3)
If strKop = "000" Then
strKop = "only"
Else
strKop = "and FILS " & strKop & " only"
End If

strAmount = Left(strAmount, Len(strAmount) - 4)
If strAmount = "0" Then
    say = "NO KUWAITI DINAR "
Else
    say = "KUWAITI DINAR "
    Select Case Len(strAmount) Mod 3
        Case 1:    strAmount = "00" & strAmount
        Case 2:    strAmount = "0" & strAmount
    End Select
    For intTmp = Len(strAmount) \ 3 To 1 Step -1
        say = say & say_triada(Mid(strAmount, Len(strAmount) - 3 * intTmp + 1, 3), intTmp)
    Next
End If
say = say & strKop
End Function
Function say_triada(ByVal triada As String, ByVal triada_no As Integer) As String
Dim intTemp As Integer, strTemp  As String

If triada = "000" And triada_no > 1 Then Exit Function

say_triada = Choose(Left(triada, 1) + 1, "", "One ", "Two ", "Three ", "Four ", _
           "Five ", "Six ", "Seven ", "Eight ", "Nine ")
If say_triada <> "" Then say_triada = say_triada & "Houndred "

If Right(triada, 2) > 19 Then
    intTemp = Right(triada, 1)
    say_triada = say_triada & _
    Choose(Mid(triada, 2, 1) + 1, "", "", "Twenty ", "Thirty ", "Forty ", "Fifty ", _
                                "Sixty ", "Seventy ", "Eighty ", "Ninety ")
Else
    intTemp = Right(triada, 2)
End If
   
say_triada = say_triada & Choose(intTemp + 1, _
            "", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", _
            "Eight ", "Nine ", "Ten ", "Eleven ", "Twelve ", _
            "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", _
            "Seventeen ", "Eighteen ", "Nineteen ") _
            & Choose(triada_no, "", "Thousand", "Million", "Billion", "Trillion")
End Function
---------
Cheers!
Dedushka
0
 
LVL 7

Expert Comment

by:Dedushka
Comment Utility
Sorry, bakery.

Change this:

& Choose(triada_no, "", "Thousand", "Million", "Billion", "Trillion")

to

& Choose(triada_no, "", "Thousand ", "Million ", "Billion ", "Trillion ")

Dedushka
0
 

Author Comment

by:bakery
Comment Utility

Sir Dedushka's

Your currency conver answer is realy very Excellent, it works without any problem, I thank u once again and can I have your email address if any other help i need from u Pls.

shekar

0
 
LVL 7

Expert Comment

by:Dedushka
Comment Utility
Shekar, you welcome!

khamed@istnet.ru

Dedushka
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now