Here's a function that should extract a string of 7 digits beginning with 67 from a field.
Code:
Function get67(sfld As String) As String
'chk field not null
'chk field contains 67
'chk 7 chars for numeric
'chk for trimmed length = 7
10 On Error GoTo get67_Error
20 get67 = vbNullString
30 If Not IsNull(sfld) Then
40 If InStr(sfld, "67") > 0 Then
50 If IsNumeric(Mid(sfld, InStr(sfld, "67"), 7)) Then
60 If Len(Trim(Mid(sfld, InStr(sfld, "67"), 7))) = 7 Then
70 get67 = Mid(sfld, InStr(sfld, "67"), 7)
'Debug.Print "acct number is " & Mid(sfld, InStr(sfld, "67"), 7)
80 End If
90 End If
100 End If
110 End If
120 On Error GoTo 0
130 Exit Function
get67_Error:
140 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure get67 "
End Function
If the string is not found, the default value returned by get67() is vbNullString
Try it using get67(accounts)