How to convert number to string in Access?

siamandm
5StarLounger
Posts: 1002
Joined: 01 May 2016, 09:58

How to convert number to string in Access?

Post by siamandm »

Hello all,
if I want to convert numbers to string how to do it, please?

for example, I have a field for numbers like

2
202
490
4999

I want to convert them to text
two
two hundred and two
four hundred nighty
..

User avatar
HansV
Administrator
Posts: 69824
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to convert number to string in Access?

Post by HansV »

Copy the following code into a module in the Visual Basic Editor:

Code: Select all

Function SpellNumber(ByVal MyNumber As Variant) As String
    Dim Dollars As String, Temp As String
    Dim Count As Long
    Dim Minus As String
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    If IsNull(MyNumber) Then
        Exit Function
    End If
    If MyNumber < 0 Then
        Minus = "Minus "
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Dollars
        Case ""
            Dollars = "Zero"
    End Select
    SpellNumber = Minus & Dollars
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber As String) As String
    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 As String) As String
    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 As String, Optional ShowZero As Boolean) As String
    Select Case Val(Digit)
        Case 0
            If ShowZero Then
                GetDigit = "Zero"
            End If
        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"
    End Select
End Function

' Spells digits
Function SpellDigits(s As String) As String
    Dim i As Long
    Dim Result As String
    For i = 1 To Len(s)
        Result = Result & " " & GetDigit(Mid(s, i, 1), True)
    Next i
    SpellDigits = Trim(Result)
End Function
Let's say that you have a field named Amount in a table.
In a query based on the table, you can add a column

TextAmount: SpellNumber([Amount])

Or set the Control Source of a text box on a form or report to

=SpellNumber([Amount])
Regards,
Hans

siamandm
5StarLounger
Posts: 1002
Joined: 01 May 2016, 09:58

Re: How to convert number to string in Access?

Post by siamandm »

thank you very much for the reply, its working fine but I have to do a modification in order to make it work better with my language
for example
in English we have
20= twenty
21 = twenty one
but in my language
20 = twenty
but from 21 = we have to say = twenty and one
and for 31 we have to say thirty and one
so basically we have to add (( and )) between the two numbers ( twenty ) and 1, 2,3 ...

so we have to have another case for numbers 21 to 29 , 31 to 39 , 41 to 49 and so one


we have the same issue for 101 ... it is not one hundred one .. it is one hundred and one
220 two hundred and twenty
221 two hundred and twenty and one

it is complicated !!
is there is a way to work around this?


regards

User avatar
HansV
Administrator
Posts: 69824
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How to convert number to string in Access?

Post by HansV »

It's a bit of a kludge, but try this:

Code: Select all

Function SpellNumber(ByVal MyNumber As Variant) As String
    Dim Dollars As String, Temp As String
    Dim Count As Long
    Dim Minus As String
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    If IsNull(MyNumber) Then
        Exit Function
    End If
    If MyNumber < 0 Then
        Minus = "Minus "
    End If
    Count = 1
    Do While MyNumber <> ""
        Temp = GetHundreds(Right(MyNumber, 3))
        If Temp <> "" Then
            Dollars = Temp & Place(Count) & " And " & Dollars
        End If
        If Len(MyNumber) > 3 Then
            MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        Else
            MyNumber = ""
        End If
        Count = Count + 1
    Loop
    Select Case Dollars
        Case ""
            Dollars = "Zero"
        Case Else
            Dollars = Application.Trim(Dollars)
            Dollars = Replace(Dollars, "And And", "And")
            If Left(Dollars, 4) = "And " Then
                Dollars = Mid(Dollars, 5)
            End If
            If Left(Dollars, 5) = " And " Then
                Dollars = Mid(Dollars, 6)
            End If
            If Right(Dollars, 4) = " And" Then
                Dollars = Left(Dollars, Len(Dollars) - 4)
            End If
            Dollars = Replace(Dollars, "  ", " ")
    End Select
    SpellNumber = Minus & Dollars
End Function

' Converts a number from 100-999 into text
Function GetHundreds(ByVal MyNumber As String) As String
    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) <> "00" Then
        If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & " And " & GetTens(Mid(MyNumber, 2))
        Else
            Result = Result & "And " & GetDigit(Mid(MyNumber, 3))
        End If
    End If
    GetHundreds = Result
End Function

' Converts a number from 10 to 99 into text.
Function GetTens(TensText As String) As String
    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 & " And " & 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 As String, Optional ShowZero As Boolean) As String
    Select Case Val(Digit)
        Case 0
            If ShowZero Then
                GetDigit = "Zero"
            End If
        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"
    End Select
End Function

' Spells digits
Function SpellDigits(s As String) As String
    Dim i As Long
    Dim Result As String
    For i = 1 To Len(s)
        Result = Result & " " & GetDigit(Mid(s, i, 1), True)
    Next i
    SpellDigits = Trim(Result)
End Function
Regards,
Hans