Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract number (2003)

    In a query is there a way to extract a number from a field? I have the following - Individual 300 dollars in a field. I want to extract the number in this field. Can this be done? Thanks for your help..

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract number (2003)

    Will there be one number at most in the field, or could there be more than one (for example "between 100 and 120 dollars")? If the latter, what would you want to do with the numbers?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract number (2003)

    there will be only one number.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract number (2003)

    Copy the following function into a standard module:
    <code>
    Function ExtractNumber(varValue As Variant) As Variant
    Dim i As Integer
    Dim c As String
    Dim strRet As String
    If IsNull(varValue) Then
    ExtractNumber = Null
    Exit Function
    End If
    For i = 1 To Len(varValue)
    c = Mid(varValue, i, 1)
    If IsNumeric© Then
    strRet = strRet & c
    End If
    Next i
    If strRet = "" Then
    ExtractNumber = 0
    ' Alternative:
    ' ExtractNumber = Null
    Else
    ExtractNumber = CLng(strRet)
    End If
    End Function
    </code>
    You can use it in a query as follows:
    <code>
    TheNumber: ExtractNumber([FieldName])
    </code>
    This version will return null (blank) if the field value is null. If you always want to return a number, with 0 being returned if the field value is null, you can use this slightly shorter function:
    <code>
    Function ExtractNumber(varValue As Variant) As Long
    Dim i As Integer
    Dim c As String
    Dim strRet As String
    If IsNull(varValue) Then
    ExtractNumber = 0
    Exit Function
    End If
    strRet = "0"
    For i = 1 To Len(varValue)
    c = Mid(varValue, i, 1)
    If IsNumeric© Then
    strRet = strRet & c
    End If
    Next i
    ExtractNumber = CLng(strRet)
    End Function</code>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract number (2003)

    Thanks Hans. Looks like it will work perfectly.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract number (2003)

    Spoke too soon. I used the second function that you sent. i put it into the query like so - DeductAmt: ExtractNumber([Expr2]). I wanted to put >499 in the criteria but It wouldn't let me so I created another query and brought that query into it and then I could put >499 for that field. I connected that query to other queries in another query (sounds confusing) and ran the Transfer database macro to create a table. I went to a 4 hour meeting and when I came back there was an error highlighting the following line of the function - ExtractNumber = clng(strRet). I didn't catch what the error was but it was something like not enough space. I am running it again with the extract calculation but now not making another query so I can put >499. There is a lot of data.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract number (2003)

    Functions like these aren't very efficient if you have a large amount of data.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract number (2003)

    I wanted to thank you for the extract function. I got it to work. It is a lifesaver. Thanks again.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract number (2003)

    You're welcome! Glad you were able to use it.

Posting Permissions

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