Validating a string format in VBA Access

Question

• I would like to write a Boolean function that checks that 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 should be entered manually into the text box.

So it's either 9999999 or the required Medicaid formatted string that return a value of True.

Samples:

AZ12345Z
NP54321J
EM17345P

So far I have 2 functions working together but I made a mess of the logic!!

Thank you

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

While RegEx is a good general solution for this type of problem, in this case a simple Like comparison will do

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