title: Validating a string format in VBA Access
body: <ul>
  <li>I would like to write a Boolean function that checks that Medicaid IDs are in the required format.</li>
  <li>Namely 2 alpha characters followed by 5 digits followed by 1 alpha character. </li>
  <li>If the Medicaid ID is not available then 99999999 should be entered manually into the text box. </li>
  </ul>
  
  <p>So it's either 9999999 or the required Medicaid formatted string that return a value of True. </p>
  
  <p>Samples: </p>
  
  <blockquote>
    <p>AZ12345Z<br>
     NP54321J<br>
     EM17345P</p>
  </blockquote>
  
  <p>So far I have 2 functions working together but I made a mess of the logic!!</p>
  
  <p>Thank you</p>
  
  <pre><code>Public Function isAlpha(cChar As Integer) As Boolean
  'returns true if its a alphabetic character
      isAlpha = IIf((cChar &gt;= 65 And cChar &lt;= 90) Or (cChar &gt;= 97 And cChar &lt;= 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
  </code></pre>