Solved

Currency Convert Formula

Posted on 1998-11-30
8
746 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
ID: 1969588
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
ID: 1969589
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
ID: 1969590
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
ID: 1969591
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 7

Accepted Solution

by:
Dedushka earned 50 total points
ID: 1969592
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
ID: 1969593
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
ID: 1969594

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
ID: 1969595
Shekar, you welcome!

khamed@istnet.ru

Dedushka
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I create a table in a linked database, in VB? 7 17
MS Access Tables Linking 6 43
how to link subforms ms/access VBA 6 30
Access Query based on a Form Field 2 21
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

863 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

24 Experts available now in Live!

Get 1:1 Help Now