I need to test a field to determine if it has a 7 digit number combination that begins with a "10", 20", or "80' and if it does, return that 7 digit number combination. A function to do this would be great. Is this possible?
If IsNumber(Me![CombNum]) Then
If Len(Me![CombNum]) = 7 and (Cstr(Left(Me![CombNum],2)) = "10" Or Cstr(Left(Me![CombNum],2)) = "20" Or Cstr(Left(Me![CombNum],2)) = "80") Then
MsgBox "You Win"
Else
MsgBox "You Lose"
End If
Else
MsgBox "Invalied Number"
End If
Public Function FindAndCapturePO(infield As String) As String
Dim X As Integer
Dim CNT As Integer
Dim TARGET As String
CNT = Len(infield) - 6
X = 0
Start:
If X < CNT Then
X = X + 1
Else: GoTo ENDIT
End If
TARGET = Mid(infield, X, 7)
If TARGET Like "10#####" Or TARGET Like "20#####" Or TARGET Like "80#####" Then
FindAndCapturePO = TARGET
GoTo ENDIT
Else: GoTo Start
End If
ENDIT:
End Function
Public Function FindAndCapturePO(infield As String) As String
Dim X As Integer
Dim CNT As Integer
Dim TARGET As String
CNT = Len(infield) - 6
X = 0
Start:
If X < CNT Then
X = X + 1
Else: GoTo ENDIT
End If
If X = 1 or X = (CNT - 1) Then
TARGET = Mid(infield, X, 8)
If TARGET Like "10#####[!1-9]" Or TARGET Like "20#####[!1-9]" Or TARGET Like "80#####[1-9]" Then
FindAndCapturePO = TARGET
GoTo ENDIT
Else: GoTo Start
End If
Else
TARGET = Mid(infield, X, 9)
If TARGET Like "[!1-9]10#####[!1-9]" Or TARGET Like "[!1-9]20#####[!1-9]" Or TARGET Like "[1-9]80#####[1-9]" Then
FindAndCapturePO = TARGET
GoTo ENDIT
Else: GoTo Start
End If
End If
ENDIT:
End Function