In the database window, click on Modules. This selects the modules tab. Click
on the New button. Depending on your version of Access (I have 2003) this
opens up a window in which to edit code.
Copy and paste the following into this window:
Function Find5(InputStr As String)
Dim i, k As Integer
Dim OutputStr As String
OutputStr = ""
k = 0
For i = 1 To Len(InputStr)
If k < 5 Then
If Mid(InputStr, i, 1) >= "0" And Mid(InputStr, i, 1) <= "9" Then
OutputStr = OutputStr & Mid(InputStr, i, 1)
k = k + 1
Else
If k < 5 Then
k = 0
OutputStr = ""
End If
End If
End If
Next i
Find5 = OutputStr
End Function
Note: I added an extra If statement to prevent further processing once 5
numbers were found. If your text had "33333 text 45" then it would have
returned inaccurate results. Don't use the function from my previous post.
Click on the Save button and save the module. Module1 works, or call it
something else if you like.
In your query, create a output field "Name: Find5([Text Field])". Replace
"Name" with what you want the field to be called in the header of your output
and "Text Field" with what your text field is called in your input
Table/Query.
This should give you a column with the 5 digit number extracted from your
text field.
Blair
rachlh22 said:
How do you incorporate functions into Access? I have been building simple
queries using the query builder. Can you give me instructions on how to use
this with a query in the query builder?