I'm having a module to convert numbers into words, but my module is not working.
What may be the reason for this?
Any help on this would be kindly appreciated.
Thanks in advance.
I've attached the database for your reference.
Printable View
I'm having a module to convert numbers into words, but my module is not working.
What may be the reason for this?
Any help on this would be kindly appreciated.
Thanks in advance.
I've attached the database for your reference.
Sorry, I can't open Access 2007 databases.
can you post the Module Code?
1. If you're using the function to set a value, it's good practice to return a value.
2. I'm not sure, but there was a problem with recognizing the function from the module. The only way I got it to work was to set the value to =ConvertCurrencyToEnglihs.ConvertCurrencyToEnglish(Me.Total)
3. I'm sure you were going to attach that to a table and have the fields bound, but if not, then I would suggest putting an After_Update event on the Total textbox. I'm attaching the changes I made that got it working for me. Feel free to inspect, toss out, or whatever.
I would urge you to adopt a better naming convention. You should never name a module the same name as any function.Quote:
Originally Posted by adamsm
I find it best to prefix all module with mod and all function name with a f
So you r module would have the name: modConvertCurrencyToEnglish
the function would have the name: fConvertCurrencyToEnglish
This way the names are unique and will not confuse Access.
Thanks for the help Imdabaum. I do really appreciate your help and thanks for the suggestion & guidance Boyd Trimmell aka HiTechCoach.
I'll follow the instructions accordingly.
I wrote a function to do this about 15 years ago (in Access 95) and have been using it ever since. It may be extremely primitive since I was just learning VBA at the time, but it is functional and easy to use and I always carry it around in my bag of tricks. It was designed for check or proposal writing and maxes out at $999,999,99. But that's easily increased.
Execute it by a simple statement like:
[vba]
Dim MyAmount As Variant
MyAmount = fNumericToAlpha(Price)
AlphaAmount = MyAmount
[/vba]
[vba]
Public Function fNumericToAlpha(ByRef MyNumber) As Variant
' convert a curreny field to an alpha currency amound
Dim ANum(102) As String, ConvertNum(15) As Variant, Cents As String
Dim AAmount As String, L As Long, I As Integer, Dec As Integer
Dim HundredsA As String, ThousandsA As String, TensA As String, HThousandsA As String
Dim Tens As Integer, Hundreds As Integer, Thousands As Integer, PassIt As String
Dim HThousands As Integer
ANum(1) = "One": ANum(2) = "Two": ANum(3) = "Three": ANum(4) = "Four"
ANum(5) = "Five": ANum(6) = "Six": ANum(7) = "Seven": ANum(8) = "Eight"
ANum(9) = "Nine": ANum(10) = "Ten": ANum(11) = "Eleven": ANum(12) = "Twelve"
ANum(13) = "Thirteen": ANum(14) = "Fourteen": ANum(15) = "Fifteen": ANum(16) = "Sixteen"
ANum(17) = "Seventeen": ANum(18) = "Eighteen": ANum(19) = "Nineteen": ANum(20) = "Twenty"
ANum(21) = "Twenty One": ANum(22) = "Twenty Two": ANum(23) = "Twenty Three": ANum(24) = "Twenty Four"
ANum(25) = "Twenty Five": ANum(26) = "Twenty Six": ANum(27) = "Twenty Seven": ANum(28) = "Twenty Eight"
ANum(29) = "Twenty Nine": ANum(30) = "Thirty": ANum(31) = "Thirty One": ANum(32) = "Thirty Two"
ANum(33) = "Thirty Three": ANum(34) = "Thirty Four": ANum(35) = "Thirty Five": ANum(36) = "Thirty Six"
ANum(37) = "Thirty Seven": ANum(38) = "Thirty Eight": ANum(39) = "Thirty Nine": ANum(40) = "Forty"
ANum(41) = "Forty One": ANum(42) = "Forty Two": ANum(43) = "Forty Three": ANum(44) = "Forty Four"
ANum(45) = "Forty Five": ANum(46) = "Forty Six": ANum(47) = "Forty Seven": ANum(48) = "Forty Eight"
ANum(49) = "Forty Nine": ANum(50) = "Fifty": ANum(51) = "Fifty One": ANum(52) = "Fifty Two"
ANum(53) = "Fifty Three": ANum(54) = "Fifty Four": ANum(55) = "Fifty Five": ANum(56) = "Fifty Six"
ANum(57) = "Fifty Seven": ANum(58) = "Fifty Eight": ANum(59) = "Fifty Nine": ANum(60) = "Sixty"
ANum(61) = "Sixty One": ANum(62) = "Sixty Two": ANum(63) = "Sixty Three": ANum(64) = "Sixty Four"
ANum(65) = "Sixty Five": ANum(66) = "Sixty Six": ANum(67) = "Sixty Seven": ANum(68) = "Sixty Eight"
ANum(69) = "Sixty Nine": ANum(70) = "Seventy": ANum(71) = " Seventy One": ANum(72) = "Seventy Two"
ANum(73) = "Seventy Three": ANum(74) = "Seventy Four": ANum(75) = "Seventy Five": ANum(76) = "Seventy Six"
ANum(77) = "Seventy Seven": ANum(78) = "Seventy Eight": ANum(79) = "Seventy Nine": ANum(80) = "Eighty"
ANum(81) = "Eighty One": ANum(82) = "Eighty Two": ANum(83) = "Eighty Three": ANum(84) = "Eighty Four"
ANum(85) = "Eighty Five": ANum(86) = "Eighty Six": ANum(87) = "Eighty Seven": ANum(88) = "Eighty Eight"
ANum(89) = "Eighty Nine": ANum(90) = "Ninety": ANum(91) = "Ninety One": ANum(92) = "Ninety Two"
ANum(93) = "Ninety Three": ANum(94) = "Ninety Four": ANum(95) = "Ninety Five"
ANum(96) = "Ninety Six": ANum(97) = "Ninety Seven": ANum(98) = "Ninety Eight": ANum(99) = "Ninety Nine"
ANum(100) = "Hundred": ANum(101) = "Thousand"
HThousandsA = "": ThousandsA = "": HundredsA = "": TensA = ""
AAmount = Str$(MyNumber)
L = Len(AAmount)
For I = 1 To L
If IsNull(Mid$(AAmount, I, 1)) Then GoTo IsBlank
ConvertNum(I) = Mid$(AAmount, I, 1)
IsBlank:
Next I
'find the decimal place in the array
For I = 1 To L
If ConvertNum(I) = "." Then
Dec = I
GoTo DoneFor
End If
Next
'no decimal found
Dec = L + 1
DoneFor:
' Determine Cents
If Dec > L Then
Cents = "No Cents"
GoTo EndCents
End If
If Dec + 1 > L Then
Cents = "No Cents"
GoTo EndCents
End If
If Dec + 2 > L Then
Cents = Mid$(AAmount, Dec + 1, 1) + "0 Cents"
GoTo EndCents
End If
Cents = Mid$(AAmount, Dec + 1, 2) + " Cents"
EndCents:
If MyNumber < 1 Then
fNumericToAlpha = Cents
GoTo DoneFunction
End If
If Dec - 2 > 0 Then
PassIt = ConvertNum(Dec - 2) + ConvertNum(Dec - 1)
Else
If Dec - 1 > 0 Then
PassIt = "0" + ConvertNum(Dec - 1)
Else
PassIt = "00"
End If
End If
Tens = Val(PassIt)
TensA = ANum(Tens)
If MyNumber < 1000 Then GoTo NoThousands
'is it over 100000?
If Dec - 6 > 0 Then
PassIt = ConvertNum(Dec - 6)
HThousands = Val(PassIt)
If HThousands = 0 Then
HThousandsA = ""
Else
HThousandsA = ANum(HThousands) + " " + ANum(100)
End If
Else
HThousandsA = ""
End If
If Dec - 5 > 0 Then
PassIt = ConvertNum(Dec - 5) + ConvertNum(Dec - 4)
Thousands = Val(PassIt)
Else
PassIt = ConvertNum(Dec - 4)
Thousands = Val(PassIt)
End If
ThousandsA = ANum(Thousands) + " " + ANum(101)
NoThousands:
If MyNumber < 100 Then
HundredsA = ""
GoTo NoHundreds
End If
If Dec - 3 > 0 Then
PassIt = ConvertNum(Dec - 3)
Hundreds = Val(PassIt)
Else
HundredsA = ""
GoTo NoHundreds
End If
If Hundreds = 0 Then
HundredsA = ""
GoTo NoHundreds
End If
HundredsA = ANum(Hundreds) + " " + ANum(100)
NoHundreds:
fNumericToAlpha = HThousandsA + " " + ThousandsA + " " + HundredsA + " " + TensA + " Dollars And " + Cents
DoneFunction:
End Function
[/vba]
Stick in a number like: $12,348.82
It will return:
Twelve Thousand Three Hundred Forty Eight Dollars and 82 Cents
Thanks for sharing the code!