
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com



January 14th, 2006, 07:24 AM

Authorized User


Join Date: Jan 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts


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.

January 14th, 2006, 10:43 AM

Friend of Wrox


Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts


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

January 14th, 2006, 07:14 PM

Authorized User


Join Date: Dec 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts


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.

January 15th, 2006, 05:39 AM

Authorized User


Join Date: Jan 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts


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

January 15th, 2006, 06:37 AM

Authorized User


Join Date: Dec 2005
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts


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 nonnumbers 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"

January 17th, 2006, 03:44 AM

Authorized User


Join Date: Jan 2005
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts


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


