View original (French)

Convert number amount into words

ouzzin Posted messages 268 Registration date   Status Member Last intervention   -  
 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
Configuration: Windows XP Internet Explorer 6.0

12 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    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)
    13
    1. lami20j Posted messages 21506 Registration date   Status Moderator, Security Contributor Last intervention   3 571
       
      Hello,

      I do not remember the author's name to cite it, may they forgive me.

      I think it's JvDo
      --
      106485010510997108
      0
      1. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147 > lami20j Posted messages 21506 Registration date   Status Moderator, Security Contributor Last intervention  
         
        Hi Lami20J,

        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)
        0
    2. ouzzin Posted messages 268 Registration date   Status Member Last intervention   29
       
      Thank you
      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?
      0
    3. boul
       
      Thank you for the well-done code.
      0
  2. ouzzin Posted messages 268 Registration date   Status Member Last intervention   29
     
    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
    5
  3. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    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)
    4
  4. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    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)
    1
  6. ouzzin Posted messages 268 Registration date   Status Member Last intervention   29
     
    thank you, I'm on my way
    see you later
    1
  7. ouzzin Posted messages 268 Registration date   Status Member Last intervention   29
     
    It's an Excel file that you sent me; I'm using Access.
    1
  8. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Exactly, my mistake, I skimmed through it

    See you later
    Mike-31

    A problem without a solution is a poorly posed problem (Einstein)
    1
  9. ouzzin Posted messages 268 Registration date   Status Member Last intervention   29
     
    Exactly, my bad, I skimmed through it


    HELLO
    I'm still waiting for your help with my problem on Access.

    Thank you
    0
  10. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    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)
    0
  11. ouzzin Posted messages 268 Registration date   Status Member Last intervention   29
     
    Thank you.
    0
  12. mountaga
     
    Thank you to all the members of this forum, especially Mike-31 .... also the site!!!
    Mountaga Kane from Senegal (tagakane@hotmail.com)
    0
  13. toxicjesus
     
    I’m sorry to tell you that this function doesn’t work. For example, when you put in 95, it shows = Ninety-five euros.
    0