p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Function not returning value (http://p2p.wrox.com/showthread.php?t=37188)

 civa January 14th, 2006 07:24 AM

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.

 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

 All times are GMT -4. The time now is 04:43 PM.