Returning a value from somewhere within a string

[clive2002]

I need a formula to return a 7 diget number from with a text field, the data comes in the format below.

"License Search.Could be any value in here or blank.6680997.PUNTO 60 ELX 5 DR.Could be any value in here or blank."

This is quite easy to do in excel because the value is the only 7 digit number between two "." as below.

=MID(A1,(SEARCH(".???????.",A1,1)+1),7)


But access doesn't have the SEARCH formula.

instr() wont allow wild cards, how can i find out where the number i need starts???

Please help

 

[raskew]

Try this UDF:


Function NumOMatic2(ByVal pStr As String, pLen As Integer) As Long
'*******************************************
'Name: NumOMatic2 (Function)
'Purpose: Find the first n-digit number within a text screen
'From debug window:
'widget = "e any value in here or blank.6680997.PUNTO 60 ELX 5 DR.Could be any value in here or blank."
'? NumoMatic2(widget, 7)

'Returns: 6680997
'*******************************************


Dim strHold As String, midHold As String, numHold As String
Dim strLen As Integer, intLen As Integer, n As Integer

strHold = pStr
intLen = Len(strHold) - pLen - 1
numHold = ""
For n = 1 To intLen
midHold = Mid(strHold, n, 1)
If IsNumeric(midHold) Then
numHold = numHold & midHold
If Len(numHold) = pLen Then
NumOMatic2 = numHold
Exit For
End If
Else
numHold = ""
End If
Next n

End Function

 

[clive2002]

Really i'm looking for something i can pop in a column in a query to return the number. There must be some kind of formula to tell me the position of the second "." or where the ".#######." appears.