Solved NEED TO WRITE AMOUNT IN WORDS

[ssparalkar]

Hi,

I am not very well versed with access but created a small file in access for generating proforma.

I've created a report for final printing of proforma and on that report I need to show grand total (which is a calculated control) in words.
I've downloaded some code from Microsoft Forum and it worked well in excel but when I put it in access as VB module, it is showing error.
If someone can help on this.
I've attached code which I copied and screenshot for better understanding.

Thanks in advance.

 

[jdraw]

To write amount in words(English) I have this routine that you are welcome to use/adjust or ignore.
I did not review your code. I went strictly by the thread title.

Note: I used this to respond to a poster and it solved his/her issue at the time.

'---------------------------------------------------------------------------------------
' Procedure : Convert
' Author    : mellon
' Date      : 31-Oct-2016
' Purpose   : Copied from
'http://msaccessvb.blogspot.ca/2011/02/number-to-words-conversion-with-ms.html
'and adjusted to deal with dollars and cents from Pesos and Centavos

'---------------------------------------------------------------------------------------
'
Function Convert(ByVal mAmount As String) As String
          Dim mDec As Variant, mDecWhat As String, mNumberOfDigits, mCntr, Word, OneD, TwoD, mInput
10        On Error GoTo Convert_Error

20        mInput = Format(mAmount, "####0.00")
30        OneD = Array("", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine ", "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen ")
40        TwoD = Array("", "Ten ", "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety ")
50        mDec = InStr(1, mInput, ".", 1)
60        If mDec = 0 Then mAmount = mInput    'no decimal
70        If mDec <> 0 Then mDecWhat = Right(mInput, Len(mInput) - mDec): mAmount = mInput: mInput = Mid(mInput, 1, mDec - 1)
80        If Len(mInput) > 12 Then Exit Function
90        If Len(mInput) <= 3 Then mNumberOfDigits = Array(mInput, "", "", "")
100       If Len(mInput) >= 4 And Len(mInput) <= 6 Then mNumberOfDigits = Array(Mid(mInput, 1, Len(mInput) - 3), Right(mInput, 3), "", "")
110       If Len(mInput) >= 7 And Len(mInput) <= 9 Then mNumberOfDigits = Array(Mid(mInput, 1, Len(mInput) - 6), Mid(mInput, Len(mInput) - 5, 3), Right(mInput, 3), "")
120       If Len(mInput) >= 10 And Len(mInput) <= 12 Then mNumberOfDigits = Array(Mid(mInput, 1, Len(mInput) - 9), Mid(mInput, Len(mInput) - 8, 3), Mid(mInput, Len(mInput) - 5, 3), Right(mInput, 3))
130       For mCntr = 0 To IIf(Len(mInput) <= 3, 0, IIf(Len(mInput) >= 4 And Len(mInput) <= 6, 1, IIf(Len(mInput) >= 7 And Len(mInput) <= 9, 2, IIf(Len(mInput) >= 10 And Len(mInput) <= 12, 3, 0))))
140           If Len(mNumberOfDigits(mCntr)) = 3 Then
150               Word = Word & OneD(val(Left(mNumberOfDigits(mCntr), 1))) & IIf(Left(mNumberOfDigits(mCntr), 1) <> "0", "Hundred", "")
160               If Mid(mNumberOfDigits(mCntr), 2, 2) <= 19 Then Word = Word & " " & OneD(val(Mid(mNumberOfDigits(mCntr), 2))) Else If Mid(mNumberOfDigits(mCntr), 2, 1) >= 1 Then Word = Word & " " & TwoD(val(Mid(mNumberOfDigits(mCntr), 2, 1))) & IIf(Right(mNumberOfDigits(mCntr), 1) >= 1, OneD(Right(mNumberOfDigits(mCntr), 1)), "")
170           ElseIf Len(mNumberOfDigits(mCntr)) = 2 Then
180               If val(mNumberOfDigits(mCntr)) <= 19 Then Word = Word & " " & OneD(val(mNumberOfDigits(mCntr))) Else If val(mNumberOfDigits(mCntr)) >= 2 Then Word = Word & " " & TwoD(val(Left(mNumberOfDigits(mCntr), 1))) & IIf(Right(mNumberOfDigits(mCntr), 1) >= 1, OneD(val(Right(mNumberOfDigits(mCntr), 1))), "")
190           ElseIf Len(mNumberOfDigits(mCntr)) = 1 Then
200               Word = Word & " " & OneD(val(mNumberOfDigits(mCntr)))
210           End If
220           If Len(mInput) >= 4 And Len(mInput) <= 6 Then Word = Word & IIf(mCntr = 0 And mNumberOfDigits(0) > 0, " Thousand ", "")
230           If Len(mInput) >= 7 And Len(mInput) <= 9 Then Word = Word & IIf(mCntr = 0 And mNumberOfDigits(0) > 0, " Million ", IIf(mCntr = 1 And mNumberOfDigits(1) > 0, "Thousand ", ""))
240           If Len(mInput) >= 10 And Len(mInput) <= 12 Then Word = Word & IIf(mCntr = 0 And mNumberOfDigits(0) > 0, " Billion ", IIf(mCntr = 1 And mNumberOfDigits(1) > 0, "Million ", IIf(mCntr = 2 And mNumberOfDigits(2) > 0, "Thousand ", "")))
250       Next
          '==
          '-------------JED Oct 31 2016
260      ' Word = Word & IIf(val(mAmount) = 1, " Dollar ", " Dollars ")
         Word = Word & IIf(Mid(mAmount, 1, InStr(mAmount, ".") - 1) = 1, "Dollar ", "Dollars ")
270       If Right(mAmount, 2) > 0 Then
280           Word = Word & " and "
290       Else
300           Word = Word
310       End If

          '-------------JED Oct 31 2016
          '==
320       If mDec <> 0 Then
330           If val(mDecWhat) >= 1 And val(mDecWhat) <= 19 Then
340               Word = Word & OneD(val(mDecWhat)) & IIf(val(mDecWhat) = 1, " Cent", " Cents")
350           ElseIf val(mDecWhat) >= 2 Then
360               Word = Word & TwoD(val(Left(mDecWhat, 1))) & IIf(Right(mDecWhat, 1) >= 1, " " & OneD(val(Right(mDecWhat, 1))) & " Cents", " Cents")
370           End If
380       End If
390       If val(mAmount) > 0 Then
              'commented out part of line below to remove the presentation and P peso --- JED
400           Word = Word    '& " (P" & Format(mAmount, "##,##0.00") & ")": mInput = Format(mAmount, "##,##0.00")
410       End If
420       Convert = LTrim(Word): Word = ""

430       On Error GoTo 0
440       Exit Function

Convert_Error:

450       MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure Convert of Module ModuleTesting_CanKill"
End Function

Sample:

?convert("75,123.81")
Seventy Five Thousand One Hundred Twenty Three Dollars and Eighty One Cents

?convert("3,456,567")
Three Million Four Hundred Fifty Six Thousand Five Hundred Sixty Seven Dollars

?convert("123,456,567.33")
One Hundred Twenty Three Million Four Hundred Fifty Six Thousand Five Hundred Sixty Seven Dollars and Thirty Three Cents

 

[June7]

Here is Access VBA https://accessdatabasetutorial.com/convert-numbers-words-access-vba-function/

 

[arnelgp]

instead of writing NumToWord(GT), replace "GT" with the actual calculation you have for "GT".

 

[vba_php]

I've downloaded some code from Microsoft Forum and it worked well in excel but when I put it in access as VB module, it is showing error.

ss,

your code looks fine. it's not really professional, but you're fine with what you have. however, you need to change at least one line. THIS:

ReDim Place(9) As String

is un-necessary. you are assigning static values to your 8-element array on the code lines that follow that declaration. you need to do this instead:

dim place(9) as string

REDIM() is for dynamics. plus, it also needs to be accompanied by the keyword PRESERVE() in most cases, which is also meant for dynamics.

 

[ssparalkar]

Thank you all for your suggestions.
I tried everything but couldn't go through it. At last I've sent that file to someone who is a programmer.

I think I've to learn more about vb before I actually start using access.