Convert amount into words

**Emmanuel** · 07-14-2022, 11:03 AM

I have an access database report which has two textboxes linked to a table.
The textboxes are

1. Amount
2. Amount_in_Words

I will like any amount display in the report from my table to display in words in my report.

The currency used “Ghana Cedis”

Any help with this will be greatly appreciated

**CJ_London** · 07-14-2022, 11:20 AM

See this link
https://www.accessforums.net/showthread.php?t=302

plenty of other examples if you Google ‘vba convert currency to words’ here’s another example
https://www.livetolearn.in/site/down...oad-vba-script

**ranman256** · 07-14-2022, 11:21 AM

paste the code into a module ,
then the afterupdate event is:

usage:

Code:

Private Sub Amount_AfterUpdate()
Amount_in_Words = SpellNum(Amount)
End Sub

paste into module'

Code:

'NO COMMAS
Public Function SpellNum(Optional ByVal psNum)
Dim vTxt, vNum, vWord, vN1, vN2
Dim X As Integer, l As Integer, i As Integer
Dim vCents
If IsMissing(psNum) Then
   SpellNum = ""
   Exit Function
End If
If InStr(psNum, ".") > 0 Then
     i = InStr(psNum, ".")
     vCents = Mid(psNum, i + 1)
     psNum = Left(psNum, i - 1)
End If

If Len(psNum) = 1 And Val(psNum) = 0 Then
   SpellNum = "ZERO "
   Exit Function
End If

If Val(psNum) = 0 Then
  i = -1
Else
  i = Len(psNum)
End If
Select Case i
Case -1
   SpellNum = ""
   Exit Function
Case 1
    X = 0
    Select Case Val(psNum)
    Case 0
        vTxt = ""
    Case 1
        vTxt = "ONE "
    Case 2
        vTxt = "TWO "
    Case 3
        vTxt = "THREE "
    Case 4
        vTxt = "FOUR "
    Case 5
        vTxt = "FIVE "
    Case 6
        vTxt = "SIX "
    Case 7
        vTxt = "SEVEN "
    Case 8
        vTxt = "EIGHT "
    Case 9
        vTxt = "NINE "
    End Select
    
Case 2
    X = 0
    Select Case Val(psNum)
       Case 10
         vTxt = "TEN "
       Case 11
         vTxt = "ELEVEN "
       Case 12
         vTxt = "TWELVE "
       Case 13
         vTxt = "THIRTEEN "
       Case 14
         vTxt = "FORTEEN "
       Case 15
         vTxt = "FIFTEEN "
       Case 16
         vTxt = "SIXTEEN "
       Case 17
         vTxt = "SEVENTEEN "
       Case 18
         vTxt = "EIGHTEEN "
       Case 19
         vTxt = "NINETEEN "
       Case Else
          Select Case Val(Left(psNum, 1))
            Case 2
                vTxt = "TWENTY "
            Case 3
                vTxt = "THIRTY "
            Case 4
                vTxt = "FORTY "
            Case 5
                vTxt = "FIFTY "
            Case 6
                vTxt = "SIXTY "
            Case 7
                vTxt = "SEVENTY "
            Case 8
                vTxt = "EIGHTY "
            Case 9
                vTxt = "NINETY "
          End Select
          
          X = Val(Right(psNum, 1))
          vTxt = vTxt & SpellNum(X)
          X = 0
    End Select
Case 3
    X = 2
    vTxt = psNum
    vWord = "HUNDRED "
Case 4, 5, 6
    X = 3
    vWord = "THOUSAND "
Case 7, 8, 9
    X = 6
    vWord = "MILLION "
Case 10, 11, 12
    X = 9
    vWord = "BILLION "
End Select
If X > 0 Then
    l = Len(psNum) - X
    vN1 = Left(psNum, l)
    vN2 = Mid(psNum, l + 1)
    vTxt = SpellNum(vN1) & vWord
    If vTxt = "ZERO HUNDRED " Then vTxt = ""
    vTxt = vTxt & SpellNum(vN2)
End If
If Len(vCents) > 0 Then
   SpellNum = vTxt & " and " & vCents & " cents"
Else
   SpellNum = vTxt
End If
End Function

**Emmanuel** · 07-14-2022, 11:32 AM

Originally Posted by CJ_London

See this link
https://www.accessforums.net/showthread.php?t=302

plenty of other examples if you Google ‘vba convert currency to words’ here’s another example
https://www.livetolearn.in/site/down...oad-vba-script

Thanks for your reply.
Will go through it and give you feedback

**Emmanuel** · 07-14-2022, 11:34 AM

Originally Posted by ranman256

paste the code into a module ,
then the afterupdate event is:

usage:

Code:

Private Sub Amount_AfterUpdate()
Amount_in_Words = SpellNum(Amount)
End Sub

paste into module'

Code:

'NO COMMAS
Public Function SpellNum(Optional ByVal psNum)
Dim vTxt, vNum, vWord, vN1, vN2
Dim X As Integer, l As Integer, i As Integer
Dim vCents
If IsMissing(psNum) Then
   SpellNum = ""
   Exit Function
End If
If InStr(psNum, ".") > 0 Then
     i = InStr(psNum, ".")
     vCents = Mid(psNum, i + 1)
     psNum = Left(psNum, i - 1)
End If

If Len(psNum) = 1 And Val(psNum) = 0 Then
   SpellNum = "ZERO "
   Exit Function
End If

If Val(psNum) = 0 Then
  i = -1
Else
  i = Len(psNum)
End If
Select Case i
Case -1
   SpellNum = ""
   Exit Function
Case 1
    X = 0
    Select Case Val(psNum)
    Case 0
        vTxt = ""
    Case 1
        vTxt = "ONE "
    Case 2
        vTxt = "TWO "
    Case 3
        vTxt = "THREE "
    Case 4
        vTxt = "FOUR "
    Case 5
        vTxt = "FIVE "
    Case 6
        vTxt = "SIX "
    Case 7
        vTxt = "SEVEN "
    Case 8
        vTxt = "EIGHT "
    Case 9
        vTxt = "NINE "
    End Select
    
Case 2
    X = 0
    Select Case Val(psNum)
       Case 10
         vTxt = "TEN "
       Case 11
         vTxt = "ELEVEN "
       Case 12
         vTxt = "TWELVE "
       Case 13
         vTxt = "THIRTEEN "
       Case 14
         vTxt = "FORTEEN "
       Case 15
         vTxt = "FIFTEEN "
       Case 16
         vTxt = "SIXTEEN "
       Case 17
         vTxt = "SEVENTEEN "
       Case 18
         vTxt = "EIGHTEEN "
       Case 19
         vTxt = "NINETEEN "
       Case Else
          Select Case Val(Left(psNum, 1))
            Case 2
                vTxt = "TWENTY "
            Case 3
                vTxt = "THIRTY "
            Case 4
                vTxt = "FORTY "
            Case 5
                vTxt = "FIFTY "
            Case 6
                vTxt = "SIXTY "
            Case 7
                vTxt = "SEVENTY "
            Case 8
                vTxt = "EIGHTY "
            Case 9
                vTxt = "NINETY "
          End Select
          
          X = Val(Right(psNum, 1))
          vTxt = vTxt & SpellNum(X)
          X = 0
    End Select
Case 3
    X = 2
    vTxt = psNum
    vWord = "HUNDRED "
Case 4, 5, 6
    X = 3
    vWord = "THOUSAND "
Case 7, 8, 9
    X = 6
    vWord = "MILLION "
Case 10, 11, 12
    X = 9
    vWord = "BILLION "
End Select
If X > 0 Then
    l = Len(psNum) - X
    vN1 = Left(psNum, l)
    vN2 = Mid(psNum, l + 1)
    vTxt = SpellNum(vN1) & vWord
    If vTxt = "ZERO HUNDRED " Then vTxt = ""
    vTxt = vTxt & SpellNum(vN2)
End If
If Len(vCents) > 0 Then
   SpellNum = vTxt & " and " & vCents & " cents"
Else
   SpellNum = vTxt
End If
End Function

Thanks for your reply.
Let me apply these codes as you’ve instructed and give you feedback on it

**Emmanuel** · 07-14-2022, 01:00 PM

Originally Posted by ranman256

paste the code into a module ,
then the afterupdate event is:

usage:

Code:

Private Sub Amount_AfterUpdate()
Amount_in_Words = SpellNum(Amount)
End Sub

paste into module'

Code:

'NO COMMAS
Public Function SpellNum(Optional ByVal psNum)
Dim vTxt, vNum, vWord, vN1, vN2
Dim X As Integer, l As Integer, i As Integer
Dim vCents
If IsMissing(psNum) Then
   SpellNum = ""
   Exit Function
End If
If InStr(psNum, ".") > 0 Then
     i = InStr(psNum, ".")
     vCents = Mid(psNum, i + 1)
     psNum = Left(psNum, i - 1)
End If

If Len(psNum) = 1 And Val(psNum) = 0 Then
   SpellNum = "ZERO "
   Exit Function
End If

If Val(psNum) = 0 Then
  i = -1
Else
  i = Len(psNum)
End If
Select Case i
Case -1
   SpellNum = ""
   Exit Function
Case 1
    X = 0
    Select Case Val(psNum)
    Case 0
        vTxt = ""
    Case 1
        vTxt = "ONE "
    Case 2
        vTxt = "TWO "
    Case 3
        vTxt = "THREE "
    Case 4
        vTxt = "FOUR "
    Case 5
        vTxt = "FIVE "
    Case 6
        vTxt = "SIX "
    Case 7
        vTxt = "SEVEN "
    Case 8
        vTxt = "EIGHT "
    Case 9
        vTxt = "NINE "
    End Select
    
Case 2
    X = 0
    Select Case Val(psNum)
       Case 10
         vTxt = "TEN "
       Case 11
         vTxt = "ELEVEN "
       Case 12
         vTxt = "TWELVE "
       Case 13
         vTxt = "THIRTEEN "
       Case 14
         vTxt = "FORTEEN "
       Case 15
         vTxt = "FIFTEEN "
       Case 16
         vTxt = "SIXTEEN "
       Case 17
         vTxt = "SEVENTEEN "
       Case 18
         vTxt = "EIGHTEEN "
       Case 19
         vTxt = "NINETEEN "
       Case Else
          Select Case Val(Left(psNum, 1))
            Case 2
                vTxt = "TWENTY "
            Case 3
                vTxt = "THIRTY "
            Case 4
                vTxt = "FORTY "
            Case 5
                vTxt = "FIFTY "
            Case 6
                vTxt = "SIXTY "
            Case 7
                vTxt = "SEVENTY "
            Case 8
                vTxt = "EIGHTY "
            Case 9
                vTxt = "NINETY "
          End Select
          
          X = Val(Right(psNum, 1))
          vTxt = vTxt & SpellNum(X)
          X = 0
    End Select
Case 3
    X = 2
    vTxt = psNum
    vWord = "HUNDRED "
Case 4, 5, 6
    X = 3
    vWord = "THOUSAND "
Case 7, 8, 9
    X = 6
    vWord = "MILLION "
Case 10, 11, 12
    X = 9
    vWord = "BILLION "
End Select
If X > 0 Then
    l = Len(psNum) - X
    vN1 = Left(psNum, l)
    vN2 = Mid(psNum, l + 1)
    vTxt = SpellNum(vN1) & vWord
    If vTxt = "ZERO HUNDRED " Then vTxt = ""
    vTxt = vTxt & SpellNum(vN2)
End If
If Len(vCents) > 0 Then
   SpellNum = vTxt & " and " & vCents & " cents"
Else
   SpellNum = vTxt
End If
End Function

Your codes kind of do the trick.
But I have a little issue.

If I enter a figure like 750, it will be displayed as “seven hundred and fifty zero.

I also tried 750,000

And it displays as Seven Hundred and fifty zero thousand.

How do I make the zero which should not be there to be taken off?

Also, I will like it to be displayed something like “seven hundred and fifty Ghana Cedis” for an amount written as “750.00 and also

“Seven hundred and fifty Ghana cedis, fifty pesetas” for an amount written as “750.50” in the narration section.

**Welshgasman** · 07-14-2022, 01:39 PM

Try this one
https://www.access-programmers.co.uk.../#post-1815502

or this one

https://www.access-programmers.co.uk.../#post-1574842

**Emmanuel** · 07-14-2022, 02:33 PM

Originally Posted by Welshgasman

Try this one
https://www.access-programmers.co.uk.../#post-1815502

or this one

https://www.access-programmers.co.uk.../#post-1574842

After I put the codes in your first link in a module, how do I make it take effect.

Am not seeing that in the link

**Emmanuel** · 07-14-2022, 02:35 PM

Originally Posted by Welshgasman

Try this one
https://www.access-programmers.co.uk.../#post-1815502

or this one

https://www.access-programmers.co.uk.../#post-1574842

How do I make it take effect in the second link too?

**Welshgasman** · 07-14-2022, 02:40 PM

Arnel shows how to test in the immediate window?
You should be able to work it out from there? After all, it is just a function?

**Emmanuel** · 07-14-2022, 04:00 PM

I saw an something in a link which seems to work the way i want it.

Number to Words | Microsoft Access Sample Code (microsoftaccessexpert.com)

i only have one issue.

An example is "203" appears as two hundred three instead of two hundred and three

**Emmanuel** · 07-14-2022, 04:01 PM

Code:

Public Function wsiSpellNumber(ByVal MyNumber)    Dim Cedis, Pesewas, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert pesewas and set MyNumber to cedis amount.
    If DecimalPlace > 0 Then
        Pesewas = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Cedis = Temp & Place(Count) & Cedis
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Cedis
        Case ""
            Cedis = ""
        Case "One"
            Cedis = "One Cedi"
         Case Else
            Cedis = Cedis & " Cedis"
    End Select
    Select Case Pesewas
        Case ""
            Pesewas = ""
        Case "One"
            Pesewas = " and One Pesewa"
              Case Else
            Pesewas = " and " & Pesewas & " Pesewas"
    End Select
    wsiSpellNumber = Cedis & Pesewas
End Function
      
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
    Dim result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        result = result & GetTens(Mid(MyNumber, 2))
    Else
        result = result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = result
End Function
      
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
    Dim result As String
    result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: result = "Ten"
            Case 11: result = "Eleven"
            Case 12: result = "Twelve"
            Case 13: result = "Thirteen"
            Case 14: result = "Fourteen"
            Case 15: result = "Fifteen"
            Case 16: result = "Sixteen"
            Case 17: result = "Seventeen"
            Case 18: result = "Eighteen"
            Case 19: result = "Nineteen"
            Case Else
        End Select
    Else                                 ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: result = "Twenty "
            Case 3: result = "Thirty "
            Case 4: result = "Forty "
            Case 5: result = "Fifty "
            Case 6: result = "Sixty "
            Case 7: result = "Seventy "
            Case 8: result = "Eighty "
            Case 9: result = "Ninety "
            Case Else
        End Select
        result = result & GetDigit _
            (Right(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = result
End Function
     
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function

**Emmanuel** · 07-14-2022, 04:04 PM

Originally Posted by Emmanuel

Code:

Public Function wsiSpellNumber(ByVal MyNumber)    Dim Cedis, Pesewas, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount.
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none.
    DecimalPlace = InStr(MyNumber, ".")
    ' Convert pesewas and set MyNumber to cedis amount.
    If DecimalPlace > 0 Then
        Pesewas = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
                  "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Cedis = Temp & Place(Count) & Cedis
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Cedis
        Case ""
            Cedis = ""
        Case "One"
            Cedis = "One Cedi"
         Case Else
            Cedis = Cedis & " Cedis"
    End Select
    Select Case Pesewas
        Case ""
            Pesewas = ""
        Case "One"
            Pesewas = " and One Pesewa"
              Case Else
            Pesewas = " and " & Pesewas & " Pesewas"
    End Select
    wsiSpellNumber = Cedis & Pesewas
End Function
      
' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber)
    Dim result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    ' Convert the hundreds place.
    If Mid(MyNumber, 1, 1) <> "0" Then
        result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(MyNumber, 2, 1) <> "0" Then
        result = result & GetTens(Mid(MyNumber, 2))
    Else
        result = result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = result
End Function
      
' Converts a number from 10 to 99 into text.
Function GetTens(TensText)
    Dim result As String
    result = ""           ' Null out the temporary function value.
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...
        Select Case Val(TensText)
            Case 10: result = "Ten"
            Case 11: result = "Eleven"
            Case 12: result = "Twelve"
            Case 13: result = "Thirteen"
            Case 14: result = "Fourteen"
            Case 15: result = "Fifteen"
            Case 16: result = "Sixteen"
            Case 17: result = "Seventeen"
            Case 18: result = "Eighteen"
            Case 19: result = "Nineteen"
            Case Else
        End Select
    Else                                 ' If value between 20-99...
        Select Case Val(Left(TensText, 1))
            Case 2: result = "Twenty "
            Case 3: result = "Thirty "
            Case 4: result = "Forty "
            Case 5: result = "Fifty "
            Case 6: result = "Sixty "
            Case 7: result = "Seventy "
            Case 8: result = "Eighty "
            Case 9: result = "Ninety "
            Case Else
        End Select
        result = result & GetDigit _
            (Right(TensText, 1))  ' Retrieve ones place.
    End If
    GetTens = result
End Function
     
' Converts a number from 1 to 9 into text.
Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "One"
        Case 2: GetDigit = "Two"
        Case 3: GetDigit = "Three"
        Case 4: GetDigit = "Four"
        Case 5: GetDigit = "Five"
        Case 6: GetDigit = "Six"
        Case 7: GetDigit = "Seven"
        Case 8: GetDigit = "Eight"
        Case 9: GetDigit = "Nine"
        Case Else: GetDigit = ""
    End Select
End Function

Would be glad if someone could get it modified to fix this issue for me

**davegri** · 07-14-2022, 05:01 PM

You may be making assumptions. Check with your bank on how written amounts should be laid out.
My bank suggests that the only AND on a check should precede the cents.
As in
One Hundred Ten Dollars and 39/100 cents.

**Emmanuel** · 07-14-2022, 11:54 PM

Originally Posted by davegri

You may be making assumptions. Check with your bank on how written amounts should be laid out.
My bank suggests that the only AND on a check should precede the cents.
As in
One Hundred Ten Dollars and 39/100 cents.

All banks here will quote that as One hundred and Ten Dollars, 50 cents

Convert amount into words

Thread Tools

Convert amount into words

Similar Threads

Paid image visible if balance is zero or invoice amount and paid amount is same

How To add Words

How multiply different amount of hours by different amount of rates. Please Help.

Convert numbers to words WITH negatives

How to automactically change the currency amount to words ?

Tags for this Thread

Posting Permissions