CODEWITHSUNDEEP
regexstringvbams-access
Maria6460
Maria6460

Reputation: 41

Validating a string format in VBA Access

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

Upvotes: 4

Views: 8331

Answers (4)

brettdj
brettdj

Reputation: 55682

Something like this

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

test function

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

Upvotes: 3

Jzz
Jzz

Reputation: 739

Regex is very suitable for this, but if you prefer not to use it (and many people do, for various reasons) it is very possible to do without.

You made a small mistake in the if statement in the CheckMediacaidIDFormat function. 'If' is mentioned twice and you forgot some parentheses. I would formulate like this:

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

Note that I use line breaks (space followed by underscore in vba) for readability and parentheses around the 'And' conditions. You can do without the line breaks if you wish, but not without the parentheses.

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

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

Upvotes: 6

Dai
Dai

Reputation: 155270

VBA has support for Regular Expressions using the COM VBScript.RegExp object, like so:

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

Upvotes: 0

Related Questions