Check string format in VBA Access

Question

• I would like to write a boolean function that checks that the Medicaid IDs are in the required format.
• Namely, 2 alpha characters, followed by 5 digits, followed by 1 alpha character.
• If the Medicaid ID is not available, then 99999999 must be entered manually in the text box.

So this is either 9999999, or the required formatted Medicaid string that returns True.

Samples:

AZ12345Z
 NP54321J
 EM17345P

So far I have two functions that work together, but I made a mess of logic!

thank

Public Function isAlpha(cChar As Integer) As Boolean
'returns true if its a alphabetic character
    isAlpha = IIf((cChar >= 65 And cChar <= 90) Or (cChar >= 97 And cChar <= 122), True, False)   
End Function

Public Function CheckMedicaidIDFormat(strMedicaidID As String) As Boolean
    Dim blnResult As Boolean
    If strMedicaidID = "99999999" or If Len(strMedicaidID) = 8 And isAlpha(Left(strMedicaidID, 2)) = True And IsNumeric(Mid(strMedicaidID, 3, 5)) = True And isAlpha(Right(strMedicaidID, 1)) = True Then 

        blnResult = True
    Else
        blnResult = False
    End If
    CheckMecicaidIDFormat = blnResult
End Function

Answer 1

RegEx , Like

Function IsValid(strIn As String) As Boolean
    IsValid = (strIn Like "[A-Z][A-Z]#####[A-Z]") Or strIn = "99999999"
End Function

Answer 2

-

Sub Test()
MsgBox IsValid("AZ12345Z")
MsgBox IsValid("1AZ12345Z")
End Sub

Function IsValid(strIn As String) As Boolean
If strIn = "99999999" Then
IsValid = True
Else
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "^[A-Z]{2}\d{5}[A-Z]$"
IsValid = .Test(strIn)
End With
End If
End Function

Answer 3

VBA COM VBScript.RegExp, :

Dim regex As VBScript.RegExp
Set regex = New VBScript.RegExp ' or CreateObject("VBScript.RegExp")
regex.Pattern = "^[A-Za-z][A-Za-z]\d\d\d\d\d[A-Za-z]$"

If medicId = "99999999" Or regex.Test( medicId ) Then
    ' code to handle valid Medicaid ID here
End If

Answer 4

Regex , ( ), .

if CheckMediacaidIDFormat. "" , . :

Public Function CheckMedicaidIDFormat(strMedicaidID As String) As Boolean
    Dim blnResult As Boolean
    If strMedicaidID = "99999999" Or _
            (Len(strMedicaidID) = 8 And _
            isAlpha(Left(strMedicaidID, 2)) = True And _
            IsNumeric(Mid(strMedicaidID, 3, 5)) = True And _
            isAlpha(Right(strMedicaidID, 1)) = True) _
            Then

        blnResult = True
    Else
        blnResult = False
    End If
    CheckMecicaidIDFormat = blnResult
End Function

, (, vba) "". , , .