Function not returning value

civa · January 14th, 2006, 07:24 AM

Function which I wrote for convertion of numbers to words is not returning the value. Please help me on this as I am new to Access vba.

Function GetWords(ByVal Amt As Currency) As String
Dim S(27) As String
Dim MW(7) As String
Dim Words As String
Dim AndFlg As Boolean
Dim Mno As String
Dim Pno As String
Dim J As Integer
    S(1) = " One"
    S(2) = " Two"
    S(3) = " Three"
    S(4) = " Four"
    S(5) = " Five"
    S(6) = " Six"
    S(7) = " Seven"
    S(8) = " Eight"
    S(9) = " Nine"
    S(10) = " Ten"
    S(11) = " Eleven"
    S(12) = " Twelve"
    S(13) = " Thirteen"
    S(14) = " Fourteen"
    S(15) = " Fifteen"
    S(16) = " Sixteen"
    S(17) = " Seventeen"
    S(18) = " Eighteen"
    S(19) = " Nineteen"
    S(20) = " Twenty"
    S(21) = " Thirty"
    S(22) = " Forty"
    S(23) = " Fifty"
    S(24) = " Sixty"
    S(25) = " Seventy"
    S(26) = " Eighty"
    S(27) = " Ninety"

    MW(1) = " Hundred"
    MW(2) = " Crore"
    MW(3) = " Lakhs"
    MW(4) = " Thousand"
    MW(5) = " Hundred"
    MW(6) = ""
    MW(7) = " Paise"
    Words = ""
    Pno = Str(Int(Amt)) + Mid(Str(Amt), Len(Str(Amt)) - 1, 2)
    AndFlg = False
    For J = 7 To 1 Step -1
        Mno = Val(IIf(J = 1 Or J = 5, Mid(Pno, Len(Pno), 1), Mid(Pno, Len(Pno) - 1, 2)))
        Select Case (Mno)
        Case Is = 0
        Words = S(Mno) & MW(J) & Words
        Case Is <= 20
        Words = S(18 + Int(Mno / 10)) & IIf(Mno Mod 10 > 0, S(Mno Mod 10), "") & MW(J) & Words
        End Select
        If Not AndFlg And Words <> "" And (Amt > 99 Or (Amt - Int(Amt) > 0 And Amt > 0.99)) Then
            If (Amt Mod 100) <> 0 Then
                Words = " and" & Words
                AndFlg = True
            End If
        End If
        Pno = Mid(Pno, 1, Len(Pno) - IIf(J = 1 Or J = 5, 1, 2))
        If Len(Trim(Pno)) = 0 Then
            Exit Function
        End If
        Next
        GetWords = Words

End Function

help me.

Bob Bedell · January 14th, 2006, 10:43 AM

Hello,

I'm not familiar enough with the Indian numbering system to really tell whats going on in your code, but here's an MS function for converting US currency to words that may help you break your function down into smaller steps:

Code:

Function ConvertCurrencyToEnglish(ByVal MyNumber)
  Dim Temp
         Dim Dollars, Cents
         Dim DecimalPlace, Count

         ReDim Place(9) As String
         Place(2) = " Thousand "
         Place(3) = " Million "
         Place(4) = " Billion "
         Place(5) = " Trillion "

         ' Convert MyNumber to a string, trimming extra spaces.
         MyNumber = Trim(Str(MyNumber))

         ' Find decimal place.
         DecimalPlace = InStr(MyNumber, ".")

         ' If we find decimal place...
         If DecimalPlace > 0 Then
            ' Convert cents
            Temp = left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
            Cents = ConvertTens(Temp)

            ' Strip off cents from remainder to convert.
            MyNumber = Trim(left(MyNumber, DecimalPlace - 1))
         End If

         Count = 1
         Do While MyNumber <> ""
            ' Convert last 3 digits of MyNumber to English dollars.
            Temp = ConvertHundreds(Right(MyNumber, 3))
            If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
            If Len(MyNumber) > 3 Then
               ' Remove last 3 converted digits from MyNumber.
               MyNumber = left(MyNumber, Len(MyNumber) - 3)
            Else
               MyNumber = ""
            End If
            Count = Count + 1
         Loop

         ' Clean up dollars.
         Select Case Dollars
            Case ""
               Dollars = "No Dollars"
            Case "One"
               Dollars = "One Dollar"
            Case Else
               Dollars = Dollars & " Dollars"
         End Select

         ' Clean up cents.
         Select Case Cents
            Case ""
               Cents = " And No Cents"
            Case "One"
               Cents = " And One Cent"
            Case Else
               Cents = " And " & Cents & " Cents"
         End Select

         ConvertCurrencyToEnglish = Dollars & Cents
End Function


Private Function ConvertDigit(ByVal MyDigit)
        Select Case Val(MyDigit)
            Case 1: ConvertDigit = "One"
            Case 2: ConvertDigit = "Two"
            Case 3: ConvertDigit = "Three"
            Case 4: ConvertDigit = "Four"
            Case 5: ConvertDigit = "Five"
            Case 6: ConvertDigit = "Six"
            Case 7: ConvertDigit = "Seven"
            Case 8: ConvertDigit = "Eight"
            Case 9: ConvertDigit = "Nine"
            Case Else: ConvertDigit = ""
         End Select

End Function

Private Function ConvertHundreds(ByVal MyNumber)
    Dim Result As String

         ' Exit if there is nothing to convert.
         If Val(MyNumber) = 0 Then Exit Function

         ' Append leading zeros to number.
         MyNumber = Right("000" & MyNumber, 3)

         ' Do we have a hundreds place digit to convert?
         If left(MyNumber, 1) <> "0" Then
            Result = ConvertDigit(left(MyNumber, 1)) & " Hundred "
         End If

         ' Do we have a tens place digit to convert?
         If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & ConvertTens(Mid(MyNumber, 2))
         Else
            ' If not, then convert the ones place digit.
            Result = Result & ConvertDigit(Mid(MyNumber, 3))
         End If

         ConvertHundreds = Trim(Result)
End Function


Private Function ConvertTens(ByVal MyTens)
          Dim Result As String

         ' Is value between 10 and 19?
         If Val(left(MyTens, 1)) = 1 Then
            Select Case Val(MyTens)
               Case 10: Result = "Ten"
               Case 11: Result = "Eleven"
               Case 12: Result = "Twelve"
               Case 13: Result = "Thirteen"
               Case 14: Result = "Fourteen"
               Case 15: Result = "Fifteen"
               Case 16: Result = "Sixteen"
               Case 17: Result = "Seventeen"
               Case 18: Result = "Eighteen"
               Case 19: Result = "Nineteen"
               Case Else
            End Select
         Else
            ' .. otherwise it's between 20 and 99.
            Select Case Val(left(MyTens, 1))
               Case 2: Result = "Twenty "
               Case 3: Result = "Thirty "
               Case 4: Result = "Forty "
               Case 5: Result = "Fifty "
               Case 6: Result = "Sixty "
               Case 7: Result = "Seventy "
               Case 8: Result = "Eighty "
               Case 9: Result = "Ninety "
               Case Else
            End Select

            ' Convert ones place digit.
            Result = Result & ConvertDigit(Right(MyTens, 1))
         End If

         ConvertTens = Result
End Function

HTH,

Bob

alastair · January 14th, 2006, 07:14 PM

Hi Civa, here's your answer and a few other points too.

1. The function doesn't return any text because "Exit Function" happens before "Getwords = Words" has run. Move the assignment in front of the exit:

        If Len(Trim(Pno)) = 0 Then
            GetWords = Words
            Exit Function
        End If
    Next

2. I'm intrigued about the function and would be interested to see some examples of the input and the output you want them to generate.

3. Some of your code, while quite correct, could be written in a more readable way! For example:

        Pno = Mid(Pno, 1, Len(Pno) - IIf(J = 1 Or J = 5, 1, 2))

is easier to read as:

   If (J=1) or (J=5) Then
      Pno = Left(Pno, Len(Pno) - 1)
   Else
      Pno = Left(Pno, Len(Pno) - 2)
   End If

Good luck with the rest.

civa · January 15th, 2006, 05:39 AM

thanks for all your respones
helo Alastair

I have moved the getwords before exit function
iam able to get value but not correct one out put value is "hundred paise" only

any more changes in code to be done

alastair · January 15th, 2006, 06:37 AM

1. I don't understand exactly what the code has to do so I'd need some samples of the input (number) and the text you want it to generate. How does the 'currency' function display 12.34, 1234.56 123456.78, for example, and what should the output of your function be for these cases?

2. Does the 'currency' format put any symbols, letters or other non-numbers in its format - if so what, and where? For example, is is RP 12.34 or perhaps 12.34 RP ?

3. I'm guessing your numbers always have a decimal point followed by two digits, is that correct? I'd need to check but I think your code may produce unexpected results if given a number ending in ".00"

civa · January 17th, 2006, 03:44 AM

hello alistar
I thank for your kind response, but your help is required once again

1. the out put for point one should be
  12.34 is Tewleve and paise thirty four
  1234.56 is one thousand two hundred thirthy four and paise fifty six
  123456.78 is one lakh twenty three thousand four hundered fifty six
               and paise seventy eight

2. currency number does not put any symbols, letters and non symbols
3. numbers are always followed by decimals

i have changed my code little bit since select case statement is not giving any value my new code is like below

Function GetWords(ByVal Amt As Currency) As String
Dim S(27) As String
Dim MW(7) As String
Dim Words As String
Dim AndFlg As Boolean
Dim Mno As String
Dim Pno As String
Dim J As Integer
    S(1) = " One"
    S(2) = " Two"
    S(3) = " Three"
    S(4) = " Four"
    S(5) = " Five"
    S(6) = " Six"
    S(7) = " Seven"
    S(8) = " Eight"
    S(9) = " Nine"
    S(10) = " Ten"
    S(11) = " Eleven"
    S(12) = " Twelve"
    S(13) = " Thirteen"
    S(14) = " Fourteen"
    S(15) = " Fifteen"
    S(16) = " Sixteen"
    S(17) = " Seventeen"
    S(18) = " Eighteen"
    S(19) = " Nineteen"
    S(20) = " Twenty"
    S(21) = " Thirty"
    S(22) = " Forty"
    S(23) = " Fifty"
    S(24) = " Sixty"
    S(25) = " Seventy"
    S(26) = " Eighty"
    S(27) = " Ninety"

    MW(1) = " Hundred"
    MW(2) = " Crore"
    MW(3) = " Lakhs"
    MW(4) = " Thousand"
    MW(5) = " Hundred"
    MW(6) = ""
    MW(7) = " Paise"
    Words = ""

    Pno = Str(Int(Amt)) + Mid(Str(Amt), Len(Str(Amt)) - 1, 2)

    AndFlg = False
    For J = 7 To 1 Step -1

    Mno = Val(IIf(J = 1 Or J = 5, Mid(Pno, Len(Pno), 1), Mid(Pno, Len(Pno) - 1, 2)))

       If Mno = 0 Then
       ElseIf Mno <= 20 Then
       Words = S(Mno) + MW(J) + Words
       Else
       Words = S(18 + Int(Mno / 10)) + IIf(Mno Mod 10 > 0, S(Mno Mod 10), "") + MW(J) + Words
       End If
       If Not AndFlg And Words <> "" And (Amt > 99 Or (Amt - Int(Amt) > 0 And Amt > 0.99)) Then
            If (Amt Mod 100) <> 0 Then
       Words = " and" + Words
                AndFlg = True
            End If
        End If
        Pno = Mid(Pno, 1, Len(Pno) - IIf(J = 1 Or J = 5, 1, 2))

        If Len(Trim(Pno)) = 0 Then
        GetWords = Words + " Only"
        Exit Function
        End If
        Next

End Function

with this above code iam able to get all values correct but the problem is doing tens & fractions

the examples with above code are as shown below

1.00 is One One Paise Only
1.15 is One and Fifteen Paise Only
12.34 Tewleve and paise thirty four
12.00 is Twelve Twelve Paise Only
1234.56 is One Thousand Two Hundred Thirty Four and Fifty Six Paise
           Only
1234.00 is One Thousand Two Hundred Thirty Four and Thirty Four Paise
           Only
123456.78 is One Lakhs Twenty Three Thousand Four Hundred Fifty Six
             and Seventy Eight Paise Only
123456.00 is One Lakhs Twenty Three Thousand Four Hundred Fifty Six
              and Fifty Six Paise Only

help required on this