Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: Newbie question on inserting function

    I have a function that I used in an excel sheet to convert digits to their word equivalent. As in 100 = One Hundred
    Could any one please tell me how to use this in Access. I have copy pasted the code into a Module but do not know how to use it in a report

    Code:
    Option Explicit
    'Main Function
    Function SpellNumber(ByVal MyNumber)
        Dim Dollars, Fils, 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 Fils and set MyNumber to dollar amount.
        If DecimalPlace > 0 Then
            Fils = 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 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 = ""
            Case "One"
                Dollars = "One Dirham"
             Case Else
                Dollars = Dollars & " Dollars"
        End Select
        Select Case Fils
            Case ""
                Fils = " and 00/100 only"
            Case "One"
                Fils = " and 1/100 only"
                  Case Else
                Fils = " and " & Fils & "/100 only"
        End Select
        SpellNumber = Dollars & Fils
    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

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,419
    Provided Answers: 7
    in a query

    wordpellNumber([feildname])

    in a text box

    =SpellNumber([feildname])

    in a Event

    Me.feildname = SpellNumber(me.feildname)
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    I am using a textbox. I get a Runtime Error '13' Type Mismatch.

    Here is excatly what I am doing
    I have a text box field1 that contains the amount
    I have another text box that I have set to SpellNumber(field1)

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,419
    Provided Answers: 7
    try taking out the ByVal in the function line
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    Tried that too. Didnt work

    I am looking to convert a total amount to its word equivalent. Would you know of any function like that. The one that I have worked perfectly in MS Excel but I am unable to use it here

  6. #6
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    I figured it out
    myNumber needs to be String and no Integer
    Thanks for your help

Posting Permissions

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