# Thread: Extract number (2003)

1. ## 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. ## 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. ## Re: Extract number (2003)

there will be only one number.

4. ## 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. ## Re: Extract number (2003)

Thanks Hans. Looks like it will work perfectly.

6. ## 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. ## Re: Extract number (2003)

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

8. ## 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. ## 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
•