Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 14th, 2006, 07:24 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default Function not returning value

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.


Reply With Quote
  #2 (permalink)  
Old January 14th, 2006, 10:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old January 14th, 2006, 07:14 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
Reply With Quote
  #4 (permalink)  
Old January 15th, 2006, 05:39 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #5 (permalink)  
Old January 15th, 2006, 06:37 AM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"
Reply With Quote
  #6 (permalink)  
Old January 17th, 2006, 03:44 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function returning an array rfstuck Pro VB 6 5 March 12th, 2008 07:35 AM
Multi returning function reguengos Pro VB 6 2 March 3rd, 2005 01:48 PM
problem in Function returning Recordset pankaj_pp Classic ASP Professional 1 June 3rd, 2004 11:23 AM
Function not returning anything... goatboy Beginning PHP 2 December 1st, 2003 12:34 PM
Returning variables from a function starsol Beginning PHP 1 September 11th, 2003 12:58 PM



All times are GMT -4. The time now is 03:51 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.