Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output Numbers as long text (Access 97 SR2)

    I want to output currancy amounts as text. E.g.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output Numbers as long text (Access 97 SR2)

    check out this funtion in the Excel forum. I have not looked to close but it will probably work in Access.

    <post#=177393 >post 177393 </post#>


    HTH

    Peter

  3. #3
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output Numbers as long text (Access 97 SR2)

    Thanks Peter. Looked at the entire thread that the post you referenced came from and like to the look of the solution you suggest. Will take a little while to copy this over and test it - will let you know how I get on. Thanks again.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output Numbers as long text (Access 97 SR2)

    If you copy to Word first then from Word to the module it should keep its formating.

    HTH

    Peter

  5. #5
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output Numbers as long text (Access 97 SR2)

    Good Idea. Unfortunately had alread cut and past and reformated by the time I got your post.

    Unfortunately Access does not recognise << Application.IsNumber(Num) >>. Have check that Excel is listed in the references so will investigate this further.

    Thnks for your help

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Output Numbers as long text (Access 97 SR2)

    Access does have a function << IsNumeric(expression) >> which you may be able to use in its place.

    Simon.

  7. #7
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output Numbers as long text (Access 97 SR2)

    Peter.
    Thanks for your help with my query. Don't know if you are interested but I have converted the routine to work in Access. Code below;

    Called using

    NumberToText(<Currency numner to convert>, "POUNDS", "PENCE")



    Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant

    Dim TMBT As Variant
    Dim sNum As String, sDec As String, sHun As String, IC As Integer
    Dim Result As String, sCurName As String, sCent As String
    Dim boPounds As Boolean

    boPounds = False
    If VarType(Num) <> 6 Then
    NumberToText = "No Valid Number Given"
    Exit Function
    End If


    If IsMissing(vCurName) Then
    sCurName = ""
    Else
    sCurName = Trim(CStr(vCurName))
    End If

    If IsMissing(vCent) Then
    sCent = ""
    Else
    sCent = Trim(CStr(vCent))
    End If


    TMBT = Array("", "THOUSAND", "MILLION", "BILLION", "TRILLION", "QUADRILLION", "QUINTILLION", "SEXTILLION")


    sNum = Format(Num, "#####0.00", "0.00")


    sDec = Right(sNum, 2)
    sNum = Left(sNum, Len(sNum) - 3)
    If sNum > 0 Then
    boPounds = True
    Else
    boPounds = False
    End If
    If CInt(sDec) <> 0 Then
    If sNum = 0 Then
    sDec = Trim(HundredsToText(CVar(sDec)) & " " & sCent)
    Else
    sDec = "AND " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
    End If
    Else
    sDec = ""
    End If


    IC = 0
    While Len(sNum) > 0
    sHun = Right(sNum, 3)
    If Len(sNum) > 3 Then
    sNum = Left(sNum, Len(sNum) - 3)
    Else
    sNum = ""
    End If
    If CInt(sHun) <> 0 Then
    Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result)
    End If

    IC = IC + 1
    Wend
    If boPounds = True Then
    Result = Trim(Result & " " & sCurName)
    End If
    Result = Trim(Result & " " & sDec)
    NumberToText = Result

    End Function

    Function HundredsToText(Num As Integer) As String
    Dim Units As Variant, Teens As Variant, Tens As Variant
    Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer
    Dim Result As String

    Units = Array("", "ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE")
    Teens = Array("TEN", "ELEVEN", "TWELVE", "THIRTEEN", "FOURTEEN", "FIFTEEN", "SIXTEEN", "SEVENTEEN", _
    "EIGHTEEN", "NINETEEN")
    Tens = Array("", "", "TWENTY", "THIRTY", "FOURTY", "FIFTY", "SIXTY", "SEVENTY", "EIGHTY", "NINETY")
    Result = ""
    IUnit = Num Mod 10
    I = Int(Num / 10)
    ITen = I Mod 10
    IHundred = Int(I / 10)
    If IHundred > 0 Then
    Result = Units(IHundred) & " HUNDRED"
    If ITen > 0 Then
    Result = Result & " AND"
    End If
    End If
    If ITen = 1 Then
    Result = Result & " " & Teens(IUnit)
    Else
    If ITen > 1 Then
    Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit))
    Else
    Result = Trim(Result & " " & Units(IUnit))
    End If
    End If

    HundredsToText = Result
    End Function

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output Numbers as long text (Access 97 SR2)

    A usefull post for a future searcher <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •