Let me share you these simple tips on how to create an ms access visual basic function that will convert numbers into words (up to billions). This function is very useful if you want a converted amount in words to appear on your voucher, checks, payslips, payroll summary report, just to name a few.
I created this function myself and I can guarantee you that it is 100% functional in ms access 2003 (i am not sure with other versions of ms access but I think it will work the same). With this very simple function, I was able to meet the needs of my clients in terms of numbers to words conversion requirement -- they were all satisfied with the result.
The function is very easy to use. Simply copy the function and then paste it in one of your modules in ms access. Now you can call this function anywhere from your ms access application (reports, forms, queries, etc).
Please try it by following these 3 simple steps:
Step #1: Copy my ms access visual basic function below and insert it into your ms access module.
I created this function myself and I can guarantee you that it is 100% functional in ms access 2003 (i am not sure with other versions of ms access but I think it will work the same). With this very simple function, I was able to meet the needs of my clients in terms of numbers to words conversion requirement -- they were all satisfied with the result.
The function is very easy to use. Simply copy the function and then paste it in one of your modules in ms access. Now you can call this function anywhere from your ms access application (reports, forms, queries, etc).
Please try it by following these 3 simple steps:
Step #1: Copy my ms access visual basic function below and insert it into your ms access module.
Step #2: create a form in ms access with two text fields on it. Name the first text field as txtNumber and the other text field as txtInwords. Change the caption of txtNumber with Number and txtInwords with In Words
Step #3: Set the AfterUpdate Event of the txtNumber text box with this visual basic command: Me.txtInwords = convert(me.txtNumber).
You may now run your form and try.
The sample visual basic codes can include trillions please let me know i will include it for you. You can email me at mfirebux@gmail.com.
If you have anything about to say about this sample ms access visual basic function please let me know -- just leave your comment here.
How about number to word conversion using Indian Numeral System? Contact me should you need this kind of conversion in MS Access.
daw ayus ni...nice blog...a great help to programmers who are beginners...:D
ReplyDeletesir i emailed you @ mfirebux@gmail.com. please read my email.. i have questions n this program... thank you so much. sorry im newbie in programming.
ReplyDeletehello Emerson, first of all ty very much for the comment and for following my blog. Here is my answer to your query re. number to word conversion in ms access.
ReplyDeleteOn the After Update Event of your txtNumber, write this command:
me.textNumber = convert(me.txtnumber)
you can place the CONVERT function within your form or in your ms access module. But it is advisable to put this function in your ms access module to make it global. this way, you can call the function anywhere -- in your form, in your report, or programmatically...
Hope this will help you. Please don't hesitate to contact me if you need some help not only on conversion. Anything about ms access, please do contact me. I will help you to the best I can. Keep up the good work and Good Luck to you and God Bless!
Thank you Sir Eric for giving this code.. a big help for me... my problem is solve.. thank you again . .
ReplyDeleteGlad to know Emerson... You are welcome.
ReplyDeleteSir,
ReplyDeleteYour"Nomber to words conversion with VB code" is simple to follow and works fine.
Hearty congradulations!
Soman.E.R.
master, Thank you for your compliments...
Deleteiam new to vb.so please send me code for convrsion of indian numerical system numbers to words.iam having ms office 2007 with vista operating system thank you
ReplyDeletehello naidu thanks for visiting my blog. below is the link of my ms access code for converting number to word for indian numerical system. hope it can help you.
Deletehttp://msaccessvb.blogspot.com/search?updated-min=2012-01-01T00:00:00-08:00&updated-max=2013-01-01T00:00:00-08:00&max-results=1
Please guide me as to how function of "Number to word conversion" can be called in a query and report
ReplyDeleteThanks a lot for valuable guide on function of "Number to word conversion".
ReplyDeleteIt is working in a form.
But I could not call it in a "Query" or "Report"
May I request you for your guidance ?
Thank you again and again in advance.
Hello vimal pandey,
Deletethank you for visiting my blog. Kindly paste the code in your ms access module, then you can call the function anywhere (in form, in query, in report).
Assuming you have a table Test with a field named Amount with a value of
198487.54
SELECT test.Amount, Convert([Amount]) AS [In Words]
FROM test;
alternatively, you can also use query design of ms access.
in column(1) select Amount
in column(2) type InWords: Convert([Amount])
save and run your query it will give you this result
198,487.54 | One Hundred Ninety Eight Thousand Four Hundred Eighty Seven
Pesos and Fifty Four Centavos (P198,487.54)
Hope this help. Thanks :)
This comment has been removed by the author.
ReplyDeleteWhat should one name the module?
ReplyDeleteThanks for visiting my blog. Any valid name will do like "Conversions" or "NumberToWordConversions". Hope this help.
Deletethanks brother it is fine thanks a lot. but still i didn't get how to call from report...
ReplyDeletethanks brother it is fine thanks a lot. but still i didn't get how to call from report... so simple. go ahead.
ReplyDeleteIsmail Muhammed
from new delhi
Sir thank you very much for visiting my blog.
DeleteHere's how:
Inside your report, create a TextBox. Position the Textbox the way you want. In the TextBox's Control Source Property, specify:
=Convert([Me.Amount])
Please take note that the Me.Amount (a TextBox) must exist inside your report. It must hold a numeric value.
The Convert is a the name of a Function for conversion.
Therefore, if you save the conversion code (provided in my blog), give it a name Convert. Your module name can be any valid name. If you name the function other than Convert, let's say, Convert_It_Now, then your TextBox's Control Source Property, should be
=Convert_It_Now([Me.Amount])
Hope this help.
Sir, please check your email. I sent you an email with the attached database. Please open it and kindly view the report. Regards.
DeleteGreat!! Sir. i received sample db. it is fine.
Deletethanks for helping to succeed to make the reports.
Ismail VKT
from New Delhi
Welcome Sir. Please feel free to email me should you have other inquiries re. ms access... I will help you to the best I can. :)
DeleteI used it... great code. One more question. Have you try it by using numbers just with cents? And when I have a number with 01 cent at the end it doesn't say ONE CENT, it says ONE CENTS. Could it be fixed?
ReplyDeleteThanks so much
Sir you are right. I missed this thing out. Yes, this can be fixed by simply replacing this line of code:
DeleteWord = Word & " and " & OneD(Val(mDecWhat)) & " Centavos"
into:
Word = Word & " and " & OneD(Val(mDecWhat)) & IIf(Val(mDecWhat) = 1, " Centavo", " Centavos")
Thank you very much for raising this question.
I also modified the code to give an exact result for only CENT/CENTS value.
DeleteJust copy and paste the code again.
This comment has been removed by the author.
ReplyDeleteHi Sir,
ReplyDeleteThe conversion script works perfectly but, the issue is about where to place or insert the word " and ".
For instance,
198,487.54 | One Hundred Ninety Eight Thousand Four Hundred Eighty Seven
Pesos and Fifty Four Centavos (P198,487.54)
should rather read
198,487.54 | One Hundred and Ninety Eight Thousand Four Hundred Eighty Seven
Pesos Fifty Four Centavos (P198,487.54).
103 | One Hundred and Three
145 | One Hundred and Forty Five
1,028 | One Thousand and Twenty Eighty Centavos (P1,028)
168,048.54 | One Hundred and Sixty Eight Thousand and Forty Eighty
Pesos Fifty Four Centavos (P168,408.54)
168,248.54 | One Hundred and Sixty Eight Thousand Two Hundred and Forty Eighty Pesos Fifty Four Centavos (P168,248.54)
Hope to hear for a fix soon.
Thank you,
Ben Arthur
Thanks for your code. But i am not able to use this function from c#.net to display the result in crystal report.
ReplyDeletePalanikumar S
The code was designed and will run on MS Access.
DeleteDear Sir,
ReplyDeleteI try it but not understanding. plz mail me step by step how to convert number to word in access.
My E-mail Id - ekalmanoranjan@gmail.com
Just follow steps 1,2 and 3. for sure it will work.
DeleteDear sir, hope you are fine, please help me as your code bangladesh currency as 100000= one lac, 150000= one lac fifty thousand, 10000000= one crore
ReplyDeletepls. give me this code
by shorab
dear Sir
ReplyDeleteFirst Thank for your Code But if my Program to Close, that your Code Not Work, how to your code work if i open my program?...Plis Help me
By andri...
Hi, Sir. I tried your code for conversion (Indian currency). But get syntax error. The highlighted portion is sent as below. Please fix it and return. Thank you.
ReplyDeleteIf Len(mInput) <= 3 Then mNumberOfDigits = Array(mInput, "", "", "") If Len(mInput) >= 4 And Len(mInput) <= 6 Then mNumberOfDigits = Array(Mid(mInput, 1, Len(mInput) - 3), Right(mInput, 3), "", "") 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), "") 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)) If Len(mInput) <= 3 Then mNumberOfDigits = Array(mInput, "", "", "") If Len(mInput) >= 4 And Len(mInput) <= 5 Then mNumberOfDigits = Array(Mid(mInput, 1, Len(mInput) - 3), Right(mInput, 3), "", "") If Len(mInput) >= 6 And Len(mInput) <= 7 Then mNumberOfDigits = Array(Mid(mInput, 1, Len(mInput) - 5), Mid(mInput, Len(mInput) - 4, 2), Right(mInput, 3), "") If Len(mInput) >= 8 And Len(mInput) <= 9 Then mNumberOfDigits = Array(Mid(mInput, 1, Len(mInput) - 7), Mid(mInput, Len(mIn
put) - 6, 2), Mid(mInput, Len(mInput) - 4, 2), Right(mInput, 3)) For mCntr = 0 To IIf(Len(mInput) <= 3, 0, IIf(Len(mInput) >= 4 And Len(mInput) <= 5, 1, IIf(Len(mInput) >= 6 And Len(mInput) <= 7, 2, IIf(Len(mInput) >= 8 And Len(mInput) <= 9, 3, 0)))) If Len(mNumberOfDigits(mCntr)) = 3 Then Word = Word & OneD(Val(Left(mNumberOfDigits(mCntr), 1))) & IIf(Left(mNumberOfDigits(mCntr), 1) <> "0", "Hundred", "")
I am New to VC. it not working for me. followed exact steps you mentioned above. Give altert message. " Microsoft Office can not fine obect "Me". If 'me' is new macro or macro group , make sure that you have saved it or you have name it corectly.
ReplyDeleteplease guide and fix it AEAP
Regards
Milind
Hi, I have tried running this code but it is showing following error . "The Object Doesn't contain automation object Me "
ReplyDeleteI need to pay you for some of your time in getting this to work. I followed the instructions in Access 2016 and get "Compile error: Method or data member not found" Then the debugger opens and on the line that reads "Me.txtInwords = Convert(Me.txtNumber)" ".txtInwords =" is highlighted. I'll pay through paypal for your time, send me a Skype at lilrob482cclub or Facebook messenger at Robert Preston Smith thanks and JESUS CHRIST bless you!
ReplyDeletesir i tried to convert number to word by copying the above program and pasting it in vb module of ms access 2007. but it is not working. Please help me to go forward.
ReplyDeletehi sir i m raj rom srilanka
ReplyDeletei tried to convert currency to tamil word in ms access
but come some errir in tamil words . if u can plz help me. sararaja7@gmailcom
Thank you! It really helped
ReplyDeletePlease sir i followed the steps and i didnt work, am using access 2013 , any suggestions?? Regards
ReplyDelete