How to convert Numbers to Words 3

[cantech2]

I have a field called GROSS in a report which is based on a mathematical calculation of other values. The value of GROSS therefore changes.

I want GROSS to display word and not numbers. Eg if GROSS is 10200, I want it to be displated as Ten Thousand Two Hundred.

I am a novice and need a clear guide that I can implement.

Thanks.
Cantech2.

 

[M8KWR]

put this into a new module

as then use

english(value)

It has been tested, hope this helps

Function English(ByVal N As Currency) As String

    Const Thousand = 1000@
    Const Million = Thousand * Thousand
    Const Billion = Thousand * Million
    Const Trillion = Thousand * Billion

    If (N = 0@) Then English = "zero": Exit Function

    Dim Buf As String: If (N < 0@) Then Buf = "negative " Else Buf = ""
    Dim Frac As Currency: Frac = Abs(N - Fix(N))
    If (N < 0@ Or Frac <> 0@) Then N = Abs(Fix(N))
    Dim AtLeastOne As Integer: AtLeastOne = N >= 1

    If (N >= Trillion) Then
        Debug.Print N
        Buf = Buf & EnglishDigitGroup(Int(N / Trillion)) & " trillion"
        N = N - Int(N / Trillion) * Trillion
        If (N >= 1@) Then Buf = Buf & " "
    End If

    If (N >= Billion) Then
        Debug.Print N
        Buf = Buf & EnglishDigitGroup(Int(N / Billion)) & " billion"
        N = N - Int(N / Billion) * Billion
        If (N >= 1@) Then Buf = Buf & " "
    End If

    If (N >= Million) Then
        Debug.Print N
        Buf = Buf & EnglishDigitGroup(N \ Million) & " million"
        N = N Mod Million
        If (N >= 1@) Then Buf = Buf & " "
    End If

    If (N >= Thousand) Then
        Debug.Print N
        Buf = Buf & EnglishDigitGroup(N \ Thousand) & " thousand"
        N = N Mod Thousand
        If (N >= 1@) Then Buf = Buf & " "
    End If

    If (N >= 1@) Then
        Debug.Print N
        Buf = Buf & EnglishDigitGroup(N)
    End If

    If (Frac = 0@) Then
        Buf = Buf
    ElseIf (Int(Frac * 100@) = Frac * 100@) Then
        If AtLeastOne Then Buf = Buf & " and "
        Buf = Buf & Format$(Frac * 100@, "00") & "/100"
    Else
        If AtLeastOne Then Buf = Buf & " and "
        Buf = Buf & Format$(Frac * 10000@, "0000") & "/10000"
    End If

    English = Buf
End Function

 

Private Function EnglishDigitGroup(ByVal N As Integer) As String

    Const Hundred = " hundred"
    Const One = "one"
    Const Two = "two"
    Const Three = "three"
    Const Four = "four"
    Const Five = "five"
    Const Six = "six"
    Const Seven = "seven"
    Const Eight = "eight"
    Const Nine = "nine"
    Dim Buf As String: Buf = ""
    Dim Flag As Integer: Flag = False

    Select Case (N \ 100)
        Case 0: Buf = "": Flag = False
        Case 1: Buf = One & Hundred: Flag = True
        Case 2: Buf = Two & Hundred: Flag = True
        Case 3: Buf = Three & Hundred: Flag = True
        Case 4: Buf = Four & Hundred: Flag = True
        Case 5: Buf = Five & Hundred: Flag = True
        Case 6: Buf = Six & Hundred: Flag = True
        Case 7: Buf = Seven & Hundred: Flag = True
        Case 8: Buf = Eight & Hundred: Flag = True
        Case 9: Buf = Nine & Hundred: Flag = True
    End Select

    If (Flag <> False) Then N = N Mod 100
    If (N > 0) Then
        If (Flag <> False) Then Buf = Buf & " "
    Else
        EnglishDigitGroup = Buf
        Exit Function
    End If

    Select Case (N \ 10)
        Case 0, 1: Flag = False
        Case 2: Buf = Buf & "twenty": Flag = True
        Case 3: Buf = Buf & "thirty": Flag = True
        Case 4: Buf = Buf & "forty": Flag = True
        Case 5: Buf = Buf & "fifty": Flag = True
        Case 6: Buf = Buf & "sixty": Flag = True
        Case 7: Buf = Buf & "seventy": Flag = True
        Case 8: Buf = Buf & "eighty": Flag = True
        Case 9: Buf = Buf & "ninety": Flag = True
    End Select

    If (Flag <> False) Then N = N Mod 10
    If (N > 0) Then
        If (Flag <> False) Then Buf = Buf & "-"
    Else
        EnglishDigitGroup = Buf
        Exit Function
    End If

    Select Case (N)
        Case 0:
        Case 1: Buf = Buf & One
        Case 2: Buf = Buf & Two
        Case 3: Buf = Buf & Three
        Case 4: Buf = Buf & Four
        Case 5: Buf = Buf & Five
        Case 6: Buf = Buf & Six
        Case 7: Buf = Buf & Seven
        Case 8: Buf = Buf & Eight
        Case 9: Buf = Buf & Nine
        Case 10: Buf = Buf & "ten"
        Case 11: Buf = Buf & "eleven"
        Case 12: Buf = Buf & "twelve"
        Case 13: Buf = Buf & "thirteen"
        Case 14: Buf = Buf & "fourteen"
        Case 15: Buf = Buf & "fifteen"
        Case 16: Buf = Buf & "sixteen"
        Case 17: Buf = Buf & "seventeen"
        Case 18: Buf = Buf & "eighteen"
        Case 19: Buf = Buf & "nineteen"
    End Select

    EnglishDigitGroup = Buf

End Function

 

[M8KWR]

If you need Pounds and Pence then use the following code in a module.

If you use cents and dollars, then change them over in the code.

to get the value use:

SpellNumber(value)

Function SpellNumber(ByVal MyNumber)
    Dim Pounds, Pence, 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 Pence and set MyNumber to Pound amount
    If DecimalPlace > 0 Then
        Pence = 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 Pounds = Temp & Place(Count) & Pounds
          If Len(MyNumber) > 3 Then
             MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
 
    Select Case Pounds
        Case ""
            Pounds = "No Pounds"
        Case "One"
            Pounds = "One Pound"
        Case Else
            Pounds = Pounds & " Pounds"
    End Select
 
    Select Case Pence
        Case ""
            Pence = " and No Pence"
        Case "One"
            Pence = " and One Cent"
        Case Else
            Pence = " and " & Pence & " Pence"
    End Select
 
    SpellNumber = Pounds & Pence
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

 

[cantech2]

Thanks but I am still lost.

I am a user and not a programmer and I would deeply appreciate your patience.

Assume I am in the design view of my report, how do create a new module.

Thanks

 

[M8KWR]

Ok here we go, come out of the report design view, and select module, where you can see tables, queries, forms, reports, macros then modules.

Select modules, create a new one and paste either one of the codes into it, then save the module, save it as module1, as prompted.

Go back into your report, where you want to have the value displayed as text go into the field. For example if your field was called "value", you need to change this to

=SpellNumber(value)

or

=english(value)

depending on which set of code you use. I would go with the SpellNumber one, slightly better.


Save the report and preview it.

If you have any more issues let me know, and i will go through it with you.

Good luck, it will work.

 

[MichaelRed]

just to add to the confusion (choices) see faq181-1740






MichaelRed

 

[cantech2]

Dear M8KWR,
I am not out of the woods yet !

1. I have succesfully create the module. Thx.
2. In the module, OBJECT reads General, while PROCEDURE reads SpellNumber. Are these alright or do they need to be changed.
3. In the properties box of the field in question, the tab called other has fields NAME, VERTICAL and TAG. I have entered =SpellNumber(gross1) in the name field. Is this alright ?

Waiting.....

 

[cantech2]

Dear M8KWR,

I am VERY VERY VERY happy !!!!

You have made my day. I discovered my error, I was to put the spellnumber in CONTROLSOURCE and not NAME field.

I am in Nigeria and was able to edit the code so that my figures are now translated to Naira and Kobo.

God bless you,
Cantech2 (Humphrey)