Convert number amount into words
ouzzin
Posted messages
268
Registration date
Status
Membre
Last intervention
-
toxicjesus -
toxicjesus -
Hello,
I created a client receipt statement, I have the amount in figures, what I would like now is to also have the same amount in words like on the receipts.
I would also like to know how to make the print sheet smaller in size.
I am using Access 2003
Thank you
I created a client receipt statement, I have the amount in figures, what I would like now is to also have the same amount in words like on the receipts.
I would also like to know how to make the print sheet smaller in size.
I am using Access 2003
Thank you
Configuration: Windows XP Internet Explorer 6.0
12 réponses
Hi,
A few years ago for my job, I got a custom function whose author I can't remember to reference, I hope they forgive me.
Paste this code into a macro module and Under Insert/Function select custom, click on the name of the code chiffrelettre, and refer to the cell where you will enter your number
Function chiffrelettre(s)
Dim a As Variant, gros As Variant
a = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", _
"Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", _
"Eighteen", "Nineteen", "Twenty", "Twenty-One", "Twenty-Two", "Twenty-Three", "Twenty-Four", _
"Twenty-Five", "Twenty-Six", "Twenty-Seven", "Twenty-Eight", "Twenty-Nine", "Thirty", "Thirty-One", _
"Thirty-Two", "Thirty-Three", "Thirty-Four", "Thirty-Five", "Thirty-Six", "Thirty-Seven", _
"Thirty-Eight", "Thirty-Nine", "Forty", "Forty-One", "Forty-Two", "Forty-Three", _
"Forty-Four", "Forty-Five", "Forty-Six", "Forty-Seven", "Forty-Eight", _
"Forty-Nine", "Fifty", "Fifty-One", "Fifty-Two", "Fifty-Three", _
"Fifty-Four", "Fifty-Five", "Fifty-Six", "Fifty-Seven", "Fifty-Eight", _
"Fifty-Nine", "Sixty", "Sixty-One", "Sixty-Two", "Sixty-Three", _
"Sixty-Four", "Sixty-Five", "Sixty-Six", "Sixty-Seven", "Sixty-Eight", _
"Sixty-Nine", "Seventy", "Seventy-One", "Seventy-Two", "Seventy-Three", _
"Seventy-Four", "Seventy-Five", "Seventy-Six", "Seventy-Seven", _
"Seventy-Eight", "Seventy-Nine", "Eighty", "Eighty-One", _
"Eighty-Two", "Eighty-Three", "Eighty-Four", "Eighty-Five", _
"Eighty-Six", "Eighty-Seven", "Eighty-Eight", "Eighty-Nine", _
"Eighty-Ten", "Eighty-One", "Eighty-Two", "Eighty-Three", _
"Eighty-Four", "Eighty-Five", "Eighty-Six", "Eighty-Seven", _
"Eighty-Eight", "Eighty-Nine")
gros = Array("", " Billions ", " Billions ", " Millions ", " Thousand ", " Euros ", " Billion ", _
" Billion ", " Million ", " Thousand ", " Euro ")
sp = Space(1)
chaine = "00000000000000"
Cent = s * 100 - (Int(s) * 100)
s = Str(Int(s)): lg = Len(s) - 1: s = Right(s, lg): lg = Len(s)
If lg < 15 Then chaine = Mid(chaine, 1, (15 - lg)) Else chaine = ""
s = chaine + s
'billions to hundreds
gp = 1
For k = 1 To 5
x = Mid(s, gp, 1): c = a(Val(x))
x = Mid(s, gp + 1, 2): d = a(Val(x))
If k = 5 Then
If t2 <> "" And c & d = "" Then mydz = " Euros " & sp: GoTo fin
If t <> "" And c = "" And d = "One" Then mydz = " One Euro " & sp: GoTo fin
If t <> "" And t2 = "" And c & d = "" Then mydz = " d'Euros " & sp: GoTo fin
If t & c & d = "" Then myct = "": mydz = "": GoTo fin
End If
If c & d = "" Then GoTo fin
If d = "" And c <> "" And c <> "One" Then mydz = c & sp & "Cents " & gros(k) & sp: GoTo fin
If d = "" And c = "One" Then mydz = "Cents " & gros(k) & sp: GoTo fin
If d = "One" And c = "" Then myct = IIf(k = 4, gros(k) & sp, "One " & gros(k + 5) & sp): GoTo fin
If d <> "" And c = "One" Then mydz = "Cent" & sp
If d <> "" And c <> "" And c <> "One" Then mydz = c & sp & "Cent" + sp
myct = d & sp & gros(k) & sp
fin:
t2 = mydz & myct
t = t & mydz & myct
mydz = "": myct = ""
gp = gp + 3
Next
d = a(Cent)
If t <> "" Then myct = IIf(Cent = 1, " Cent ", " Cents ")
If t = "" Then myct = IIf(Cent = 1, " Cent d'Euro ", " Cents d'Euro ")
If Cent = 0 Then d = "": myct = ""
chiffrelettre = t & d & myct
End Function
See you
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
A few years ago for my job, I got a custom function whose author I can't remember to reference, I hope they forgive me.
Paste this code into a macro module and Under Insert/Function select custom, click on the name of the code chiffrelettre, and refer to the cell where you will enter your number
Function chiffrelettre(s)
Dim a As Variant, gros As Variant
a = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", _
"Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", _
"Eighteen", "Nineteen", "Twenty", "Twenty-One", "Twenty-Two", "Twenty-Three", "Twenty-Four", _
"Twenty-Five", "Twenty-Six", "Twenty-Seven", "Twenty-Eight", "Twenty-Nine", "Thirty", "Thirty-One", _
"Thirty-Two", "Thirty-Three", "Thirty-Four", "Thirty-Five", "Thirty-Six", "Thirty-Seven", _
"Thirty-Eight", "Thirty-Nine", "Forty", "Forty-One", "Forty-Two", "Forty-Three", _
"Forty-Four", "Forty-Five", "Forty-Six", "Forty-Seven", "Forty-Eight", _
"Forty-Nine", "Fifty", "Fifty-One", "Fifty-Two", "Fifty-Three", _
"Fifty-Four", "Fifty-Five", "Fifty-Six", "Fifty-Seven", "Fifty-Eight", _
"Fifty-Nine", "Sixty", "Sixty-One", "Sixty-Two", "Sixty-Three", _
"Sixty-Four", "Sixty-Five", "Sixty-Six", "Sixty-Seven", "Sixty-Eight", _
"Sixty-Nine", "Seventy", "Seventy-One", "Seventy-Two", "Seventy-Three", _
"Seventy-Four", "Seventy-Five", "Seventy-Six", "Seventy-Seven", _
"Seventy-Eight", "Seventy-Nine", "Eighty", "Eighty-One", _
"Eighty-Two", "Eighty-Three", "Eighty-Four", "Eighty-Five", _
"Eighty-Six", "Eighty-Seven", "Eighty-Eight", "Eighty-Nine", _
"Eighty-Ten", "Eighty-One", "Eighty-Two", "Eighty-Three", _
"Eighty-Four", "Eighty-Five", "Eighty-Six", "Eighty-Seven", _
"Eighty-Eight", "Eighty-Nine")
gros = Array("", " Billions ", " Billions ", " Millions ", " Thousand ", " Euros ", " Billion ", _
" Billion ", " Million ", " Thousand ", " Euro ")
sp = Space(1)
chaine = "00000000000000"
Cent = s * 100 - (Int(s) * 100)
s = Str(Int(s)): lg = Len(s) - 1: s = Right(s, lg): lg = Len(s)
If lg < 15 Then chaine = Mid(chaine, 1, (15 - lg)) Else chaine = ""
s = chaine + s
'billions to hundreds
gp = 1
For k = 1 To 5
x = Mid(s, gp, 1): c = a(Val(x))
x = Mid(s, gp + 1, 2): d = a(Val(x))
If k = 5 Then
If t2 <> "" And c & d = "" Then mydz = " Euros " & sp: GoTo fin
If t <> "" And c = "" And d = "One" Then mydz = " One Euro " & sp: GoTo fin
If t <> "" And t2 = "" And c & d = "" Then mydz = " d'Euros " & sp: GoTo fin
If t & c & d = "" Then myct = "": mydz = "": GoTo fin
End If
If c & d = "" Then GoTo fin
If d = "" And c <> "" And c <> "One" Then mydz = c & sp & "Cents " & gros(k) & sp: GoTo fin
If d = "" And c = "One" Then mydz = "Cents " & gros(k) & sp: GoTo fin
If d = "One" And c = "" Then myct = IIf(k = 4, gros(k) & sp, "One " & gros(k + 5) & sp): GoTo fin
If d <> "" And c = "One" Then mydz = "Cent" & sp
If d <> "" And c <> "" And c <> "One" Then mydz = c & sp & "Cent" + sp
myct = d & sp & gros(k) & sp
fin:
t2 = mydz & myct
t = t & mydz & myct
mydz = "": myct = ""
gp = gp + 3
Next
d = a(Cent)
If t <> "" Then myct = IIf(Cent = 1, " Cent ", " Cents ")
If t = "" Then myct = IIf(Cent = 1, " Cent d'Euro ", " Cents d'Euro ")
If Cent = 0 Then d = "": myct = ""
chiffrelettre = t & d & myct
End Function
See you
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Open your file, right-click on the page tab, this will open Visual Basic Insertion/Module in this blank page paste the entire code below
Hello,
I just returned to work, I still don't understand your approach. What you call a file is the database or is it a query or a report? I copied the code into the module and named it montantLettre
In the invoices table, I have
invoice_number
amount_digits
amount_words
date
Go back to your spreadsheet and select the cell that should contain the amount in words Insert/Function, select Custom and click on chiffrelettre A dialog box will open, click on the cell in which you will enter the value in digits
I don't see where Insert/Function is and is the spreadsheet in the field "montant_lettre" of the query or the report?
Note: It's not up to us to enter in the field "montant_lettre." It's by entering in the field "montant_chiffre" that the other field gets filled.
Thank you
Hello,
Open your file, right-click on the sheet tab, this will open Visual Basic
Insert/Module in this blank page paste the entire code below
Function chiffrelettre(s)
Dim a As Variant, gros As Variant
a = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", _
"Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", _
"Eighteen", "Nineteen", "Twenty", "Twenty-One", "Twenty-Two", "Twenty-Three", "Twenty-Four", _
"Twenty-Five", "Twenty-Six", "Twenty-Seven", "Twenty-Eight", "Twenty-Nine", "Thirty", "Thirty-One", _
"Thirty-Two", "Thirty-Three", "Thirty-Four", "Thirty-Five", "Thirty-Six", "Thirty-Seven", _
"Thirty-Eight", "Thirty-Nine", "Forty", "Forty-One", "Forty-Two", "Forty-Three", _
"Forty-Four", "Forty-Five", "Forty-Six", "Forty-Seven", "Forty-Eight", _
"Forty-Nine", "Fifty", "Fifty-One", "Fifty-Two", "Fifty-Three", _
"Fifty-Four", "Fifty-Five", "Fifty-Six", "Fifty-Seven", "Fifty-Eight", _
"Fifty-Nine", "Sixty", "Sixty-One", "Sixty-Two", "Sixty-Three", _
"Sixty-Four", "Sixty-Five", "Sixty-Six", "Sixty-Seven", "Sixty-Eight", _
"Sixty-Nine", "Sixty-Ten", "Sixty-Eleven", "Sixty-Twelve", "Sixty-Thirteen", _
"Sixty-Fourteen", "Sixty-Fifteen", "Sixty-Sixteen", "Sixty-Seventeen", _
"Sixty-Eighteen", "Sixty-Nine", "Eighty", "Eighty-One", _
"Eighty-Two", "Eighty-Three", "Eighty-Four", "Eighty-Five", _
"Eighty-Six", "Eighty-Seven", "Eighty-Eight", "Eighty-Nine", _
"Eighty-Ten", "Eighty-Eleven", "Eighty-Twelve", "Eighty-Thirteen", _
"Eighty-Fourteen", "Eighty-Fifteen", "Eighty-Sixteen", "Eighty-Seventeen", _
"Eighty-Eighteen", "Eighty-Nine")
gros = Array("", " Billion ", " Billion ", " Million ", " Thousand ", " Euros ", " Billion ", _
" Billion ", " Million ", " Thousand ", " Euro ")
sp = Space(1)
chaine = "00000000000000"
Cent = s * 100 - (Int(s) * 100)
s = Str(Int(s)): lg = Len(s) - 1: s = Right(s, lg): lg = Len(s)
If lg < 15 Then chaine = Mid(chaine, 1, (15 - lg)) Else chaine = ""
s = chaine + s
'billions to hundreds
gp = 1
For k = 1 To 5
x = Mid(s, gp, 1): c = a(Val(x))
x = Mid(s, gp + 1, 2): d = a(Val(x))
If k = 5 Then
If t2 <> "" And c & d = "" Then mydz = " Euros " & sp: GoTo fin
If t <> "" And c = "" And d = "One" Then mydz = " One Euro " & sp: GoTo fin
If t <> "" And t2 = "" And c & d = "" Then mydz = " of Euros " & sp: GoTo fin
If t & c & d = "" Then myct = "": mydz = "": GoTo fin
End If
If c & d = "" Then GoTo fin
If d = "" And c <> "" And c <> "One" Then mydz = c & sp & "Cents " & gros(k) & sp: GoTo fin
If d = "" And c = "One" Then mydz = "Cents " & gros(k) & sp: GoTo fin
If d = "One" And c = "" Then myct = IIf(k = 4, gros(k) & sp, "One " & gros(k + 5) & sp): GoTo fin
If d <> "" And c = "One" Then mydz = "Cent" & sp
If d <> "" And c <> "" And c <> "One" Then mydz = c & sp & "Cent" + sp
myct = d & sp & gros(k) & sp
fin:
t2 = mydz & myct
t = t & mydz & myct
mydz = "": myct = ""
gp = gp + 3
Next
d = a(Cent)
If t <> "" Then myct = IIf(Cent = 1, " Cent ", " Cents ")
If t = "" Then myct = IIf(Cent = 1, " Cent of Euro ", " Cents of Euro ")
If Cent = 0 Then d = "": myct = ""
chiffrelettre = t & d & myct
End Function
Go back to your spreadsheet and select the cell that should contain the amount in words
Insert/Function, select Custom and click on chiffrelettre
A dialog box will open, click on the cell where you will enter the value in figures
If you encounter a problem go back to the forum, anyway let us know
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Open your file, right-click on the sheet tab, this will open Visual Basic
Insert/Module in this blank page paste the entire code below
Function chiffrelettre(s)
Dim a As Variant, gros As Variant
a = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", _
"Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", _
"Eighteen", "Nineteen", "Twenty", "Twenty-One", "Twenty-Two", "Twenty-Three", "Twenty-Four", _
"Twenty-Five", "Twenty-Six", "Twenty-Seven", "Twenty-Eight", "Twenty-Nine", "Thirty", "Thirty-One", _
"Thirty-Two", "Thirty-Three", "Thirty-Four", "Thirty-Five", "Thirty-Six", "Thirty-Seven", _
"Thirty-Eight", "Thirty-Nine", "Forty", "Forty-One", "Forty-Two", "Forty-Three", _
"Forty-Four", "Forty-Five", "Forty-Six", "Forty-Seven", "Forty-Eight", _
"Forty-Nine", "Fifty", "Fifty-One", "Fifty-Two", "Fifty-Three", _
"Fifty-Four", "Fifty-Five", "Fifty-Six", "Fifty-Seven", "Fifty-Eight", _
"Fifty-Nine", "Sixty", "Sixty-One", "Sixty-Two", "Sixty-Three", _
"Sixty-Four", "Sixty-Five", "Sixty-Six", "Sixty-Seven", "Sixty-Eight", _
"Sixty-Nine", "Sixty-Ten", "Sixty-Eleven", "Sixty-Twelve", "Sixty-Thirteen", _
"Sixty-Fourteen", "Sixty-Fifteen", "Sixty-Sixteen", "Sixty-Seventeen", _
"Sixty-Eighteen", "Sixty-Nine", "Eighty", "Eighty-One", _
"Eighty-Two", "Eighty-Three", "Eighty-Four", "Eighty-Five", _
"Eighty-Six", "Eighty-Seven", "Eighty-Eight", "Eighty-Nine", _
"Eighty-Ten", "Eighty-Eleven", "Eighty-Twelve", "Eighty-Thirteen", _
"Eighty-Fourteen", "Eighty-Fifteen", "Eighty-Sixteen", "Eighty-Seventeen", _
"Eighty-Eighteen", "Eighty-Nine")
gros = Array("", " Billion ", " Billion ", " Million ", " Thousand ", " Euros ", " Billion ", _
" Billion ", " Million ", " Thousand ", " Euro ")
sp = Space(1)
chaine = "00000000000000"
Cent = s * 100 - (Int(s) * 100)
s = Str(Int(s)): lg = Len(s) - 1: s = Right(s, lg): lg = Len(s)
If lg < 15 Then chaine = Mid(chaine, 1, (15 - lg)) Else chaine = ""
s = chaine + s
'billions to hundreds
gp = 1
For k = 1 To 5
x = Mid(s, gp, 1): c = a(Val(x))
x = Mid(s, gp + 1, 2): d = a(Val(x))
If k = 5 Then
If t2 <> "" And c & d = "" Then mydz = " Euros " & sp: GoTo fin
If t <> "" And c = "" And d = "One" Then mydz = " One Euro " & sp: GoTo fin
If t <> "" And t2 = "" And c & d = "" Then mydz = " of Euros " & sp: GoTo fin
If t & c & d = "" Then myct = "": mydz = "": GoTo fin
End If
If c & d = "" Then GoTo fin
If d = "" And c <> "" And c <> "One" Then mydz = c & sp & "Cents " & gros(k) & sp: GoTo fin
If d = "" And c = "One" Then mydz = "Cents " & gros(k) & sp: GoTo fin
If d = "One" And c = "" Then myct = IIf(k = 4, gros(k) & sp, "One " & gros(k + 5) & sp): GoTo fin
If d <> "" And c = "One" Then mydz = "Cent" & sp
If d <> "" And c <> "" And c <> "One" Then mydz = c & sp & "Cent" + sp
myct = d & sp & gros(k) & sp
fin:
t2 = mydz & myct
t = t & mydz & myct
mydz = "": myct = ""
gp = gp + 3
Next
d = a(Cent)
If t <> "" Then myct = IIf(Cent = 1, " Cent ", " Cents ")
If t = "" Then myct = IIf(Cent = 1, " Cent of Euro ", " Cents of Euro ")
If Cent = 0 Then d = "": myct = ""
chiffrelettre = t & d & myct
End Function
Go back to your spreadsheet and select the cell that should contain the amount in words
Insert/Function, select Custom and click on chiffrelettre
A dialog box will open, click on the cell where you will enter the value in figures
If you encounter a problem go back to the forum, anyway let us know
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Hi,
I thought you had given up, get an example and some explanations with the link below, I made some modifications to the code
https://www.cjoint.com/?gsvdVACCVo
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
I thought you had given up, get an example and some explanations with the link below, I made some modifications to the code
https://www.cjoint.com/?gsvdVACCVo
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Exactly, my mistake, I skimmed through it
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Exactly, my bad, I skimmed through it
HELLO
I'm still waiting for your help with my problem on Access.
Thank you
Re,
Access is not my cup of tea, check out these two links
http://www.commentcamarche.net/faq/sujet 11100 vb6 vba transform number into text
https://access.developpez.com/sources/?page=Conv#ConvNomsPropres
A+
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Access is not my cup of tea, check out these two links
http://www.commentcamarche.net/faq/sujet 11100 vb6 vba transform number into text
https://access.developpez.com/sources/?page=Conv#ConvNomsPropres
A+
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
Thank you to all the members of this forum, especially Mike-31 .... also the site!!!
Mountaga Kane from Senegal (tagakane@hotmail.com)
Mountaga Kane from Senegal (tagakane@hotmail.com)
I do not remember the author's name to cite it, may they forgive me.
I think it's JvDo
--
106485010510997108
It's possible, but JVDO, according to the link you provided, had already taken it from Exelabo, and I'm pretty sure I got it from that forum as well.
It is slightly modified with the replacement in the cell of the number by the written-out number entered in A, which can be an advantage, while the one I suggest, certainly by the same author, keeps the number and fills the chosen cell with the written-out number.
But it doesn't matter, the code works well, and regardless of its author, we should thank them; it's a shame we can't credit them for sure.
See you later
Mike-31
A problem without a solution is a poorly posed problem (Einstein)
it's a bit unclear on my end.
let's go to the field "montantchiffre"
do I need to create another field "montantLettre" in which the amount in letters will be displayed that will need to receive the macros?